多对多表加入Pivot

I currently have two tables similar to users and programs that are linked through a many-to-many relationships by way of a link table.

我目前有两个类似于用户和程序的表,这些表通过链接表通过多对多关系链接。

mysql> select * from users;
+----+----------+
| id | name     |
+----+----------+
|  1 | Jonathan |
|  2 | Little   |
|  3 | Annie    |
|  4 | Bob      |
+----+----------+
4 rows in set (0.00 sec)

mysql> select * from programs;
+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | Microsoft Word       |
|  2 | Microsoft Excel      |
|  3 | Microsoft PowerPoint |
+----+----------------------+
3 rows in set (0.00 sec)

mysql> select * from link;
+---------+------------+
| user_id | program_id |
+---------+------------+
|       1 |          1 |
|       1 |          2 |
|       1 |          3 |
|       2 |          2 |
|       3 |          1 |
|       3 |          4 |
+---------+------------+
6 rows in set (0.00 sec)

I understand how to join the tables and return a result of this sort:

我理解如何连接表并返回这种结果:

mysql> select users.name, programs.name from linker
    -> join users on users.id = linker.user_id
    -> join programs on programs.id = linker.program_id;
+----------+----------------------+
| name     | name                 |
+----------+----------------------+
| Jonathan | Microsoft Word       |
| Jonathan | Microsoft Excel      |
| Jonathan | Microsoft PowerPoint |
| Little   | Microsoft Excel      |
| Annie    | Microsoft Word       |
+----------+----------------------+

But what I am really looking for is a little more complicated:

但我真正想要的是更复杂一点:

+----------+-----------------------------------------------------+
| name     | name                                                |
+----------+-----------------------------------------------------+
| Jonathan | Microsoft Word,Microsoft Excel,Microsoft PowerPoint |
| Little   | Microsoft Excel                                     |
| Annie    | Microsoft Word                                      |
+----------+-----------------------------------------------------+

I assume there is a GROUP_CONCAT() thrown into the command somewhere, but I cannot seem to keep the results from looking like this:

我假设某个地方有一个GROUP_CONCAT()抛入命令,但我似乎无法保持结果看起来像这样:

mysql> select users.name, group_concat(programs.name) from linker
    -> join users on users.id = linker.user_id
    -> join programs on programs.id = linker.program_id;
+----------+------------------------------------------------------------------------------------+
| name     | group_concat(programs.name)                                                        |
+----------+------------------------------------------------------------------------------------+
| Jonathan | Microsoft Word,Microsoft Excel,Microsoft PowerPoint,Microsoft Excel,Microsoft Word |
+----------+------------------------------------------------------------------------------------+

Can anybody point me in the right direction?

任何人都能指出我正确的方向吗?

2 个解决方案

#1

You need to specify a DISTINCT, i.e.

你需要指定一个DISTINCT,即

select users.name, group_concat( DISTINCT programs.name)

See the MySQL docs here.

请在此处查看MySQL文档。

Try changing your query to:

尝试将您的查询更改为:

SELECT users.name, group_concat(programs.name) 
from users
LEFT JOIN linker on linker.user_id = users.id
LEFT JOIN programs on linker.program_id = programs.id
GROUP BY users.id

This will give you a null for any user with no programs associated with them. To filter them out, just add a WHERE programs.id IS NOT NULL.

这将为没有与之关联的程序的任何用户提供null。要过滤掉它们,只需添加一个WHERE programs.id IS NOT NULL。

#2

SELECT users.name, group_concat(programs.name) from linker
INNER JOIN users on linker.user_id = users.id
INNER JOIN programs on linker.program_id = programs.id
GROUP BY users.id;

#1