mysql - 同一个表上的多个 LEFT JOIN 语句

Question

我尝试多次对同一个表进行 LEFT JOIN，以获取特定主题的所有值。它的工作原理就像我想象的那样，请参阅:http://sqlfiddle.com/#!9/9cda67/4

但是，使用上面的 fiddle ，数据库为每个不同的类(class)返回一行。我想使用 GROUP BY PersonID 对它们进行分组，但它只会采用第一个值(数学为 6)和所有的 (null) 值其他列。请参阅:http://sqlfiddle.com/#!9/9cda67/5

我需要更改什么才能让每个人获得单行，并将所有成绩填写到各自的列中(如果可用)？

Answer 1

MySQL 允许您在 SELECT 中包含不在 GROUP BY 中的列。这实际上违反了 ANSI 标准，并且不受任何其他数据库支持(尽管在某些情况下 ANSI 标准确实允许)。结果是输出中单行的不确定值。

解决方案是聚合函数:

SELECT p.id AS PersonID, p.name AS PersonName,
       max(pc1.grade) AS Math,
       max(pc2.grade) AS Chemistry,
       max(pc3.grade) AS Physics
FROM Person p LEFT JOIN
     Person_Course pc
     on p.id = pc.user_id LEFT JOIN
     Course c on c.id = pc.course_id LEFT JOIN
     Person_Course pc1
     on pc1.id = pc.id AND pc1.course_id = 1 LEFT JOIN 
     Person_Course pc2
     on pc2.id = pc.id AND pc2.course_id = 2 LEFT JOIN 
     Person_Course pc3
     on pc3.id = pc.id AND pc3.course_id = 3 
GROUP BY PersonID;

如果人们可以多次学习同一门类(class)，您可能需要group_concat()。另外，不要对列名称使用单引号。仅将它们用于字符串和日期常量。

将类(class) ID 硬连接到代码中似乎是个坏主意。我会使用条件聚合更简单地编写此内容:

SELECT p.id AS PersonID, p.name AS PersonName,
       max(case when c.name = 'Math' then pc.grade end) AS Math,
       max(case when c.name = 'Chemistry' then pc.grade end) AS Chemistry,
       max(case when c.name = 'Physics' then pc.grade end) AS Physics
FROM Person p LEFT JOIN
     Person_Course pc
     on p.id = pc.user_id LEFT JOIN
     Course c
     on c.id = pc.course_id
GROUP BY PersonID;