sql - 如何使用sql将多行值作为多列的单行返回

Question

静态表中有三行称为“SUBJECT”

SubjectID       SubjectCode
---------------------------
    1           MATHS
    2           SCIENCE
    3           ENGLISH
---------------------------

我的应用程序会将相应学生的每个科目分数的条目放在另一个名为“MARKS”的表格中

MarkID      StudentID       SubjectID       Marks
---------------------------------------------------
    1           1               1           90
    2           1               2           85
    3           1               3           80
    4           2               1           100
    5           2               2           70
    6           2               3           90

我正在努力创建一个虚拟表，如下所示:

StudentID       Maths       Science     English 
------------------------------------------------
    1           90          85          80
    2           100         70          90

我试过查询

Select  MARKS.StudentID,
        CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.Marks END as Maths, 
        CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.Marks END as Science, 
        CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.Marks END as English, 
        FROM 
        MARKS 
        JOIN SUBJECTS on SUBJECTS.SubjectID = MARKS.SubjectID
        GROUP BY 
        MARKS.StudentID, SUBJECTS.SubjectCode, MARKS.Marks

但它返回，

StudentID       Maths       Science     English 
------------------------------------------------
    1           90          NULL        NULL
    1           NULL        85          NULL
    1           NULL        NULL        80
    2           100         NULL        NULL
    2           NULL        70          NULL
    2           NULL        NULL        90

Answer 1

你很近。你只需要聚合:

SELECT MARKS.StudentID,
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.MARK END) as Maths, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.MARK END) as Science, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.MARK END) as English, 
FROM MARKS JOIN
     SUBJECTS 
     ON SUBJECTS.SubjectID = MARKS.SubjectID
GROUP BY MARKS.StudentID
ORDER BY MARKS.StudentID;

我建议您使用表别名，以便查询更易于编写和阅读:

SELECT m.StudentID,
       MAX(CASE WHEN s.SubjectCode = 'MATHS' THEN M.MARK END) as Maths, 
       MAX(CASE WHEN s.SubjectCode = 'SCIENCE' THEN M.MARK END) as Science, 
       MAX(CASE WHEN s.SubjectCode = 'ENGLISH' THEN M.MARK END) as English, 
FROM MARKS m JOIN
     SUBJECTS s
     ON s.SubjectID = m.SubjectID
GROUP BY m.StudentID
ORDER BY m.StudentID;