如何优化从同一表同列查询多行数据的SQL 以学生成绩行转列为例

现有方案的不足 #

当前两次LEFT JOIN的写法逻辑是通顺的，但存在明显的性能短板：每多查询一个科目就要多关联一次CourseGrades表，当表内数据量较大时，多表关联带来的IO开销会成倍增长，查询效率会快速下降；而且原SQL中ScienceGrade列后面多了多余的逗号，会直接触发语法报错。

性能更优的实现方案 #

推荐使用条件聚合+单次左连接的写法，只需要扫描一次CourseGrades表就能拿到所有需要的科目成绩，SQL如下：

SELECT 
    s.FirstName,
    MAX(CASE WHEN cg.courseName = 'maths' THEN cg.Grade END) AS MathsGrade,
    MAX(CASE WHEN cg.courseName = 'science' THEN cg.Grade END) AS ScienceGrade
FROM 
    [Student] s
LEFT JOIN 
    [CourseGrades] cg ON s.pkid = cg.SID 
    AND cg.courseName IN ('maths', 'science')
GROUP BY 
    s.pkid, s.FirstName

方案优势 #

• 仅需对CourseGrades表做1次关联扫描，数据量越大，相比多JOIN方案的性能优势越突出；后续如果要新增查询科目，只需要新增对应的CASE分支即可，不需要额外加关联逻辑，扩展性更好。
• 可以搭配联合索引进一步优化：给CourseGrades表创建(SID, courseName, Grade)的联合覆盖索引，查询时不需要回表读取数据，性能还能再提升。

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