使用SQL PIVOT运算符汇总成绩时如何将总分转换为预期单科分数

问题原因 #

当前返回的各科数值刚好是预期值的2倍，说明你的源表Grade_Report中每个学生的每门科目存在2条成绩记录，使用SUM聚合后得到的是两次成绩的总和，你需要的是单科的平均成绩。

解决方案 #

将PIVOT子句中的聚合函数从SUM替换为AVG即可，修改后的查询语句如下：

SELECT
    Student, [English], [Mathematics], [Science], [Programming], [History]
FROM
    (SELECT Student, Grades, Subject 
     FROM Grade_Report) AS SOURCETABLE
PIVOT
    (AVG(Grades) 
        FOR Subject IN ([English], [Mathematics], [Science], [Programming], [History])
    ) AS PIVOTTABLE

可选替代方案 #

如果你的统计逻辑是固定取总和除以2，也可以直接在查询字段中做除法运算，写法如下：

SELECT
    Student, 
    [English]/2 AS [English], 
    [Mathematics]/2 AS [Mathematics], 
    [Science]/2 AS [Science], 
    [Programming]/2 AS [Programming], 
    [History]/2 AS [History]
FROM
    (SELECT Student, Grades, Subject 
     FROM Grade_Report) AS SOURCETABLE
PIVOT
    (SUM(Grades) 
        FOR Subject IN ([English], [Mathematics], [Science], [Programming], [History])
    ) AS PIVOTTABLE

内容的提问来源于stack exchange，提问作者William Rowell Gimena2à2ĎĖ3