mysql - 与mysql水平连接

Question

我正在尝试查询 2 个表，一个包含学生，另一个包含与学生相关的评论。

学生表:

| ID | Name |
-------------
| 1  | John |
| 2  | Alex |

评论表:

| ID | Student ID | Comment |
-----------------------------
| 1  | 1          | phoned  |
| 2  | 1          | emailed |
| 3  | 2          | phoned  |
| 4  | 2          | emailed |
| 5  | 2          | mailed  |

我想要的输出是这样的:

| Student | Comment 3 | Comment 2 | Comment 1 |
-----------------------------------------------
| John    | null      | phoned    | emailed   |
| Alex    | mailed    | emailed   | called    |

如有任何帮助，我们将不胜感激。

Answer 1

一种方法如下:

SELECT students.name, c1.comment, c2.comment, c3.comment FROM students
LEFT JOIN (SELECT * FROM comments WHERE comment = 'phoned') as c1
ON c1.student_id = students.id
LEFT JOIN (SELECT * FROM comments WHERE comment = 'emailed') as c2
ON c2.student_id = students.id
LEFT JOIN (SELECT * FROM comments WHERE comment = 'mailed') as c3
ON c3.student_id = students.id

您必须增加每种评论类型的加入数量。