寻求MySQL多空列合并为单列的高效解决方案

首先看你当前的SQL，因为缺少GROUP BY和聚合函数，返回的结果是每个课程对应一行，其他课程列都是空值——这显然不是你想要的“单个表中”的聚合结果。我们先修正这个行转列的逻辑，再实现合并单列的需求。

第一步：正确实现行转列（每个学生一行，课程成绩为列） #

用MAX()聚合CASE语句的结果，这样每个学生的所有课程成绩会合并到一行，未修读的课程列显示为空字符串：

SELECT 
    students.MatricNo,
    MAX(CASE WHEN courses.Code = 'KHE 101' THEN results.Total ELSE '' END) AS 'KHE101',
    MAX(CASE WHEN courses.Code = 'KHE 102' THEN results.Total ELSE '' END) AS 'KHE102',
    MAX(CASE WHEN courses.Code = 'KHE 103' THEN results.Total ELSE '' END) AS 'KHE103',
    MAX(CASE WHEN courses.Code = 'KHE 104' THEN results.Total ELSE '' END) AS 'KHE104',
    MAX(CASE WHEN courses.Code = 'TEE 103' THEN results.Total ELSE '' END) AS 'TEE103',
    MAX(CASE WHEN courses.Code = 'TEE 128' THEN results.Total ELSE '' END) AS 'TEE128',
    MAX(CASE WHEN courses.Code = 'GCE 101' THEN results.Total ELSE '' END) AS 'GCE101',
    MAX(CASE WHEN courses.Code = 'KHE 105' THEN results.Total ELSE '' END) AS 'KHE105',
    MAX(CASE WHEN courses.Code = 'KHE 107' THEN results.Total ELSE '' END) AS 'KHE107',
    MAX(CASE WHEN courses.Code = 'KHE 108' THEN results.Total ELSE '' END) AS 'KHE108',
    MAX(CASE WHEN courses.Code = 'KHE 109' THEN results.Total ELSE '' END) AS 'KHE109',
    MAX(CASE WHEN courses.Code = 'TEE 102' THEN results.Total ELSE '' END) AS 'TEE102',
    MAX(CASE WHEN courses.Code = 'GES 107' THEN results.Total ELSE '' END) AS 'GES107',
    MAX(CASE WHEN courses.Code = 'SPE 104' THEN results.Total ELSE '' END) AS 'SPE104'
FROM results
INNER JOIN students ON results.Student = students.Guid
INNER JOIN courses ON results.Course = courses.Guid
INNER JOIN departments ON students.Department = departments.Guid
WHERE departments.Code = 'KHE' AND results.Level = 100
GROUP BY students.MatricNo;

第二步：将多列合并为单列（自动忽略空值） #

如果要把每个学生的所有非空成绩合并成一个单列（比如用逗号分隔），可以用MySQL的CONCAT_WS()函数——它会自动跳过空字符串，不会产生多余的分隔符。

方案1：合并课程代码+成绩（可读性更强） #

SELECT 
    students.MatricNo,
    CONCAT_WS(', ',
        MAX(CASE WHEN courses.Code = 'KHE 101' THEN CONCAT('KHE101: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 102' THEN CONCAT('KHE102: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 103' THEN CONCAT('KHE103: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 104' THEN CONCAT('KHE104: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'TEE 103' THEN CONCAT('TEE103: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'TEE 128' THEN CONCAT('TEE128: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'GCE 101' THEN CONCAT('GCE101: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 105' THEN CONCAT('KHE105: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 107' THEN CONCAT('KHE107: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 108' THEN CONCAT('KHE108: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 109' THEN CONCAT('KHE109: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'TEE 102' THEN CONCAT('TEE102: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'GES 107' THEN CONCAT('GES107: ', results.Total) ELSE '' END),
        MAX(CASE WHEN courses.Code = 'SPE 104' THEN CONCAT('SPE104: ', results.Total) ELSE '' END)
    ) AS student_scores
FROM results
INNER JOIN students ON results.Student = students.Guid
INNER JOIN courses ON results.Course = courses.Guid
INNER JOIN departments ON students.Department = departments.Guid
WHERE departments.Code = 'KHE' AND results.Level = 100
GROUP BY students.MatricNo;

方案2：仅合并成绩值 #

如果只需要成绩的拼接，去掉课程代码的部分即可：

SELECT 
    students.MatricNo,
    CONCAT_WS(', ',
        MAX(CASE WHEN courses.Code = 'KHE 101' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 102' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 103' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 104' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'TEE 103' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'TEE 128' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'GCE 101' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 105' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 107' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 108' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'KHE 109' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'TEE 102' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'GES 107' THEN results.Total ELSE '' END),
        MAX(CASE WHEN courses.Code = 'SPE 104' THEN results.Total ELSE '' END)
    ) AS student_scores
FROM results
INNER JOIN students ON results.Student = students.Guid
INNER JOIN courses ON results.Course = courses.Guid
INNER JOIN departments ON students.Department = departments.Guid
WHERE departments.Code = 'KHE' AND results.Level = 100
GROUP BY students.MatricNo;

注意事项 #

• 如果results.Total可能为NULL而非空字符串，建议把ELSE ''改成ELSE NULL，CONCAT_WS()同样会忽略NULL值。
• 如果你需要动态适配所有课程（而非硬编码课程代码），可以考虑使用MySQL的动态SQL来生成查询语句，但这需要用到存储过程或预处理语句。

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