MySQL查询：将多表关联的学生成绩多行合并为单行

数据库表结构 #

-- schools表
id | name | city_code

-- students表
id | firstname | surname | school_id

-- subjects表
id | name | subject_code

-- class表
id | name  

-- student_score表
id | student_id | subject_id | school_id | class_id | academic_year | academic_term

需求说明 #

需要查询指定city_code的所有学校、该校学生，以及这些学生在指定学年、学期的各科成绩，要求每个学生的各科成绩合并到同一行（而非按科目分多行）。

当前查询及问题 #

目前执行以下SQL可以获取符合条件的数据，但学生成绩按科目分散在多行：

SELECT 
  A.name, 
  CONCAT_WS(' ', B.firstname, B.surname) AS fullname, 
  B.regnum AS CAS, 
  D.subject_name,
  C.ca_score, 
  C.exam_score 
FROM schools A
INNER JOIN students B ON A.id = B.school_id 
LEFT JOIN student_score C ON C.student_id = B.id 
LEFT JOIN subjects D ON D.id = C.subject_id  
WHERE 
  A.city_code = 5 AND 
  C.academic_term = 'First' AND 
  C.academic_year = '2023' AND 
  C.class_id = 9;

期望结果 #

结果结构 #

school_name | student_name | subject_code_one_ca_score | subject_code_one_exam_score | subject_code_two_ca_score | subject_code_two_exam_score | subject_code_three_ca_score | subject_code_three_exam_score | subject_code_four_ca_score | subject_code_four_exam_score

示例数据 #

遇到的问题 #

1. 尝试用GROUP BY合并学生数据时，触发错误：GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
2. 不知道如何将subject_code与成绩列名（ca_score、exam_score）拼接成类似english_ca_score的列名

解决方案 #

核心思路：条件聚合行转列 #

使用CASE WHEN结合聚合函数（如MAX()）实现行转列，同时严格遵循only_full_group_by的要求，将所有非聚合字段加入GROUP BY。

完整SQL示例 #

假设科目subject_code分别为english、maths、physics、chemistry，对应的SQL如下：

SELECT 
  A.name AS school_name,
  CONCAT_WS(' ', B.firstname, B.surname) AS student_name,
  -- 英语成绩
  MAX(CASE WHEN D.subject_code = 'english' THEN C.ca_score END) AS english_ca_score,
  MAX(CASE WHEN D.subject_code = 'english' THEN C.exam_score END) AS english_exam_score,
  -- 数学成绩
  MAX(CASE WHEN D.subject_code = 'maths' THEN C.ca_score END) AS maths_ca_score,
  MAX(CASE WHEN D.subject_code = 'maths' THEN C.exam_score END) AS maths_exam_score,
  -- 物理成绩
  MAX(CASE WHEN D.subject_code = 'physics' THEN C.ca_score END) AS physics_ca_score,
  MAX(CASE WHEN D.subject_code = 'physics' THEN C.exam_score END) AS physics_exam_score,
  -- 化学成绩
  MAX(CASE WHEN D.subject_code = 'chemistry' THEN C.ca_score END) AS chemistry_ca_score,
  MAX(CASE WHEN D.subject_code = 'chemistry' THEN C.exam_score END) AS chemistry_exam_score
FROM schools A
INNER JOIN students B ON A.id = B.school_id
LEFT JOIN student_score C ON C.student_id = B.id 
  AND C.academic_term = 'First' 
  AND C.academic_year = '2023' 
  AND C.class_id = 9
LEFT JOIN subjects D ON D.id = C.subject_id
WHERE A.city_code = 5
GROUP BY 
  A.name,
  CONCAT_WS(' ', B.firstname, B.surname),
  B.regnum -- 如果需要保留CAS字段，也要加入GROUP BY
ORDER BY A.name, student_name;

关键说明 #

1. 行转列实现：通过CASE WHEN匹配对应科目的subject_code，用MAX()聚合函数提取该科目的成绩（因为每个学生对应每个科目只有一条成绩，MAX/AVG/MIN结果一致）。
2. 解决GROUP BY错误：将SELECT中所有非聚合的字段（school_name、student_name、B.regnum）全部加入GROUP BY子句，符合only_full_group_by的要求。
3. 条件优化：将student_score的筛选条件（学年、学期、班级）从WHERE移到LEFT JOIN的ON子句中，避免过滤掉没有成绩的学生（如果需要保留这类学生的话）。
4. 动态科目扩展：如果科目不固定，需要动态生成SQL，可以通过查询subjects表获取所有subject_code，再拼接对应的CASE WHEN语句。

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