如何将单表自连接实现三表关联效果并转换数据展示格式？

问题描述 #

现有一张存储学生各学期成绩的表（结构大致为studentid、subject、term、score），需要将每行对应单学期成绩的格式，转换为按科目分组、同一行显示多学期成绩的格式。

原SQL存在的问题 #

你写的SQL有几个关键问题：

• 多表左连接仅匹配studentid，未关联subject和term，会导致同一学生的不同科目、学期数据交叉，产生笛卡尔积，结果混乱
• GROUP BY subject但未对score字段使用聚合函数，不符合SQL标准（部分宽松模式的数据库可能执行，但结果不可控）
• 存在笔误：term_tetm应为third_term

正确实现方案 #

方案1：使用自连接 #

通过多次左连接，每次连接时明确匹配studentid、subject和对应学期，确保每个连接只获取目标学期的成绩：

SELECT 
    f.subject,
    f.score AS first_term,
    s.score AS second_term,
    t.score AS third_term
FROM 
    `table` f
LEFT JOIN 
    `table` s ON f.studentid = s.studentid AND f.subject = s.subject AND s.term = 'second_term'
LEFT JOIN 
    `table` t ON f.studentid = t.studentid AND f.subject = t.subject AND t.term = 'third_term'
WHERE 
    f.studentid = '001' AND f.term = 'first_term'

方案2：使用CASE WHEN+聚合函数（更高效） #

这种写法无需多次自连接，通过分组聚合直接实现行转列，性能更优：

SELECT 
    subject,
    MAX(CASE WHEN term = 'first_term' THEN score END) AS first_term,
    MAX(CASE WHEN term = 'second_term' THEN score END) AS second_term,
    MAX(CASE WHEN term = 'third_term' THEN score END) AS third_term
FROM 
    `table`
WHERE 
    studentid = '001'
GROUP BY 
    subject

注：这里用MAX是因为每个科目每个学期仅一条成绩记录，用MIN或SUM也能得到相同结果，根据实际数据情况选择即可。

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