该查询将输出考试1和考试3中通过的学生的详细信息3.我想在每次考试中显示出他们获得的标记.如何

Question

如何更新下面包含加入和子查询的SQL查询，以便我可以获得 exam> exam> exam> exam> 和 evight = 3 在最终结果集中？

SELECT DISTINCT st.StudentID,st.Name 
FROM Students st 
INNER JOIN StudentMarks stm ON st.StudentID=stm.StudentID
WHERE st.StudentID IN(SELECT stm.StudentID 
                      FROM StudentMarks stm 
                      INNER JOIN Exam ex ON stm.ExamID=ex.ExamID
                      WHERE stm.Mark>=ex.MinMarkForPass AND stm.ExamID=1
INTERSECT
SELECT stm.StudentID 
FROM StudentMarks stm 
INNER JOIN Exam ex ON stm.ExamID=ex.ExamID
WHERE stm.Mark >= ex.MinMarkForPass AND stm.ExamID=3)

Answer 1

不需要INTERSECT，您可以做到这一点：

WITH BothCTE
(
    SELECT stm.StudentID 
    FROM StudentMarks stm 
    INNER JOIN Exam ex ON stm.ExamID=ex.ExamID
    WHERE stm.Mark >= ex.MinMarkForPass AND stm.ExamID IN(1, 3)
    GROUP BY StudentID
    HAVING(DISTINCT ExamID) >= 2
)
SELECT st.StudentID, st.Name, stm.Mark
FROM Students st 
INNER JOIN StudentMarks stm ON st.StudentID = stm.StudentID
WHERE st.StudentID IN(SELECT StudentID FROM BothCTE);

CTE将为您提供两个考试的学生，然后您可以将这些结果与分数表一起获得学生的分数。