mysql - SQL操作数执行单行、列

Question

enter image description here 我的sql查询:-

SELECT 
(s.Mark*0.8+((i.OutOf)*sum(i.Mark)/9000)+((ai.OutOf)*sum(ai.Mark) /180)+a.Mark)  
AS final 
from mark_semester s,
     mark_assignment ai,
     mark_internaltest i,
     mark_attendance a,
     master_student ms 
WHERE s.SubjectId=i.SubjectId and
      s.SubjectId=ai.SubjectId 
      and s.SubjectId=a.SubjectId 
      and s.SubjectId='1' 
      AND s.RegNo=i.RegNo 
      and s.RegNo=ai.RegNo 
      and s.RegNo=a.RegNo 
      and s.RegNo=ms.RegNo 
      and s.RegNo='100' 
      and s.Semester=i.Semester 
      and s.Semester=ai.Semester 
      and s.Semester=a.Semester 
      and s.Semester='1'

在此查询中，我想输入多个subjectid和regnos来获取数据
谁能帮我解决这个问题。

Answer 1

您可以使用 IN MySQL 运算符来处理多个 SubjectId 和 RegNo，如下所示:

SELECT (s.Mark*0.8+((i.OutOf)*sum(i.Mark)/9000)+((ai.OutOf)*sum(ai.Mark)/180)+a.Mark) AS final
from mark_semester s
    join mark_assignment ai on s.SubjectId=ai.SubjectId
    join mark_internaltest i on s.SubjectId=i.SubjectId
    join mark_attendance a on s.SubjectId=a.SubjectId
    join master_student ms on s.RegNo=ms.RegNo
WHERE
    s.SubjectId in ('1','2') AND
    s.RegNo in ('100','101') and
    s.Semester='1'