带枢轴的多对多表连接 [英] Many To Many Table Join With Pivot
问题描述
我目前有两个类似于users
和programs
的表,它们通过link
表通过多对多关系链接.
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 |
+----------+-----------------------------------------------------+
我假设命令中的某个地方抛出了GROUP_CONCAT()
,但是我似乎无法阻止结果看起来像这样:
I assume there is a GROUP_CONCAT()
thrown into the command somewhere, but I cannot seem to keep the results from looking like this:
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?
推荐答案
您需要指定DISTINCT
,即
select users.name, group_concat( DISTINCT programs.name)
请参见MySQL文档此处.
See the MySQL docs here.
尝试将查询更改为:
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
这将为没有相关程序的任何用户提供null
.要过滤掉它们,只需添加WHERE programs.id IS NOT NULL
.
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
.
这篇关于带枢轴的多对多表连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!