mysql - 在 MySQL 中使用自连接进行多重连接并按行值拆分列中的行

Question

我有三个表“Users”、“Subjects”和“Marks”

用户表

id  name
1   A
2   B
3   C
4   D
5   E
6   A
7   B

主题表

id  name
1   Chemistry
2   Physics
3   English
4   Maths
5   History

标记表

u_id是Users(id)的外键，s_id是Subjects(id)的外键

id  u_id    s_id    marks
 1     1       1     60
 2     1       2     70
 3     1       3     80
 4     2       2     80
 5     2       3     44
 6     3       1     50
 7     5       4     50
 8     4       5     50
 9     5       4    100
10     2       5    100

我希望结果是这样的

id  Name    Chemistry Physics English 
1   A       60        70      80
2   B       NULL      80      44
3   3       50        NULL    NULL

使用连接

到目前为止我只能得到

name    name        marks
A       English     80
A       Physics     70
A       Chemistry   60
B       English     44
B       Physics     80
C       Chemistry   50

使用下面的查询

SELECT u.name, s.name , m.marks
FROM Users as u 
RIGHT JOIN Marks as m ON m.u_id = u.id 
LEFT JOIN Subjects as s ON m.s_id = s.id  
WHERE s.name = "English" 
  OR s.name = "Physics" 
  OR s.name = "Chemistry"
ORDER BY u.name; "

Answer 1

好吧，看完答案后，我想发布自己的答案:

SELECT 
  u.id
, u.name

, MAX(IF(s.id = 1, COALESCE(m.mark), 0)) as 'Chem'
, MAX(IF(s.id = 2, COALESCE(m.mark), 0)) as 'Phys'
, MAX(IF(s.id = 3, COALESCE(m.mark), 0)) as 'Eng'

FROM marks m
INNER JOIN subjects s
ON s.id = m.subjects_id
INNER JOIN users u 
ON u.id = m.users_id
GROUP BY u.id

您可以在 SqlFiddle 中检查是否生成了您想要的所有内容:http://sqlfiddle.com/#!9/f567b/1

重要的部分是根据用户id对所有元素进行分组，以及将结果从一个表中的行写入另一个表中的列的方式。正如@TheShalit 回答中所写，实现这一目标的方法只是将值分配为一列。问题是，当按用户分组时，您将有很多值，您必须从中选择重要的值(既不是 0 也不是 NULL 的值，XD)。 COALESCE 函数确保您始终返回一个整数，以防出现 NULL。

同样重要的是要注意，您必须使用主题的名称和数据库中的 ID 构建 SQL，因为 SQL 无法检索元素的名称以将它们直接写入列的名称。这就是为什么我写了“Chem”、“Phys”和“Eng”而不是正确的名字。事实上，如果您只写主题的 ID 而不是名称会更容易，只是为了稍后在获取行时检索元素。

考虑到非常重要的是您的表将有正确的索引。确保你在表中有一个 UNIQUE id marks with users and subjects 以避免在那里存储多个值