如何用SQL为每位学生计算测验与作业前3高分总和

现有Marks表存储学生的测验（Quiz1-Quiz5）与作业（Assignment1-Assignment5）成绩，表结构及数据如下：

需求为：为每位学生分别选取测验的3个最高分求和得到QuizTotal，选取作业的3个最高分求和得到AssignmentTotal，预期结果如下：

尝试使用UNPIVOT但未得到预期结果，编写的SQL代码如下：

SELECT TOP(3) StudentID,  Marks 
FROM
(SELECT StudentID,CourseID , Quiz1, Quiz2, Quiz3, Quiz4, Quiz5  FROM Marks) stu

UNPIVOT

(Marks FOR QuizNo IN (Quiz1, Quiz, Quiz3, Quiz4, Quiz5)) AS mrks 

WHERE    (CourseID = 321)
Order by Marks Desc

你的代码存在三个核心问题：

1. UNPIVOT中的列名拼写错误，Quiz应为Quiz2
2. TOP(3)是全局取前3条记录，而非按学生分组取各自的前3个最高分
3. 未处理作业部分的计算，也未对筛选后的成绩做聚合求和

以下是两种可行的实现方案：

方案一：UNPIVOT + 窗口函数 #

先将测验、作业列分别转成行，用窗口函数为每个学生的成绩排名，筛选前3名后求和：

WITH QuizScores AS (
    SELECT 
        StudentID,
        CourseID,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY StudentID ORDER BY Marks DESC) AS rn
    FROM Marks
    UNPIVOT (
        Marks FOR QuizNo IN (Quiz1, Quiz2, Quiz3, Quiz4, Quiz5)
    ) AS unpvt_quiz
),
AssignmentScores AS (
    SELECT 
        StudentID,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY StudentID ORDER BY Marks DESC) AS rn
    FROM Marks
    UNPIVOT (
        Marks FOR AssignmentNo IN (Assignment1, Assignment2, Assignment3, Assignment4, Assignment5)
    ) AS unpvt_assignment
)
SELECT 
    q.StudentID,
    q.CourseID,
    SUM(q.Marks) AS QuizTotal,
    SUM(a.Marks) AS AssignmentTotal
FROM QuizScores q
JOIN AssignmentScores a ON q.StudentID = a.StudentID
WHERE q.rn <= 3 AND a.rn <=3
GROUP BY q.StudentID, q.CourseID
ORDER BY q.StudentID;

方案二：直接计算（无需UNPIVOT） #

通过VALUES子句将列值转为行集，直接取每个学生的前3个最高分求和，代码更简洁：

SELECT 
    StudentID,
    CourseID,
    -- 计算测验前3个最高分的和
    (SELECT SUM(Score) FROM (
        SELECT TOP 3 Score 
        FROM (VALUES (Quiz1), (Quiz2), (Quiz3), (Quiz4), (Quiz5)) AS Scores(Score)
        ORDER BY Score DESC
    ) AS Top3) AS QuizTotal,
    -- 计算作业前3个最高分的和
    (SELECT SUM(Score) FROM (
        SELECT TOP 3 Score 
        FROM (VALUES (Assignment1), (Assignment2), (Assignment3), (Assignment4), (Assignment5)) AS Scores(Score)
        ORDER BY Score DESC
    ) AS Top3) AS AssignmentTotal
FROM Marks
WHERE CourseID = 321
ORDER BY StudentID;

方案说明 #

• 方案一适合后续可能扩展更多成绩列的场景，通过行转列+分组排名的逻辑，扩展性更强
• 方案二更适合列数固定的场景，代码简洁直观，不需要额外的CTE（公共表表达式）

内容的提问来源于stack exchange，提问作者Mohsin