mysql - 使用桥将列组合成单列

Question

我正在使用 mysql 数据库，但我遇到了一个问题。我有一个看起来像这样的用户列表。

   Users
+----+------+
| ID | Name | 
+----+------+
|  1 | Jim  |
|  2 | Cal  |
|  3 | Rob  |
|  4 | John |
|  5 | Paul |
+----+------+

现在我还有两张 table ，

   Teams           Role
+----+----------+   +---+------+
| ID | NameTeam |   |ID | Role |
+----+----------+   +---+------+
|  1 | Team1    |   | 1 |Leader|
|  2 | Team2    |   | 2 |Member|
|  3 | Team3    |   | 3 |Role3 |
|  4 | Team4    |   | 4 |Role4 |
|  5 | Team5    |   | 5 |Role5 |
+----+----------+   +---+------+

请注意，所有 ID 都是主键。

最后有一个表格作为上面表格的桥梁。

           Bridge
+---------+--------+--------+
| ID_team | ID_user| ID_role|
+---------+--------+--------+
|  1      | 1      | 1      |
|  1      | 2      | 2      |
|  1      | 3      | 3      |
|  2      | 4      | 1      |
|  2      | 5      | 2      |
|  2      | 1      | 3      |
+---------+--------+--------+

所有这三列都是外键，链接到三个表 Users、Teams 和 Role 中的主键。

我想要的是所有用户的列表，并在一栏中列出他们所属团队的信息，以及他们在该团队中的角色。我想我可以通过左连接来做到这一点，但一个用户可能会链接到多个团队，然后该行将重复。

到目前为止我的查询:

select * from Users 
LEFT OUTER JOIN Bridge
ON Bridge.UserID = Users.UserID;

这会导致重复，例如:

    Result 
+----+------+---------+--------+--------+
| ID | Name | ID_team | ID_user| ID_role|
+----+------+---------+--------+--------+
|  1 | Jim  |  1      | 1      | 1      | << Twice Jim here
|  1 | Jim  |  2      | 1      | 3      | <<
|  2 | Cal  |  1      | 2      | 2      |
|  3 | Rob  |  1      | 3      | 3      |
|  4 | John |  2      | 4      | 1      |
|  5 | Paul |  2      | 5      | 2      |
+----+------+---------+--------+--------+


    Desired result
+----+------+----------------------------+
| ID | Name | Team and role              |
+----+------+----------------------------+
|  1 | Jim  |  Team1: Leader,Team2:role3 | <<Nice sum up.
|  2 | Cal  |  Team1: Member             |
|  3 | Rob  |  Team1: Role3              |
|  4 | John |  Team2: Leader             |
|  5 | Paul |  Team2: Member             |
+----+------+----------------------------+

这样的事情甚至可以通过单个查询实现吗？如果没有，我总是可以使用 php 创建类似的东西，但如果它可以是单个查询，那就太好了。

所以我的问题是，我怎样才能得到想要的结果？

Answer 1

您需要先连接所有表。

然后，对于您想要的输出，您需要使用 CONCAT 和 GROUP_CONCAT 函数。

SELECT 
U.ID,
U.Name,
GROUP_CONCAT(CONCAT(T.NameTeam,':',R.Role)) AS 'Team and Role'
FROM Bridge B
INNER JOIN Users U ON U.ID = B.ID_user 
INNER JOIN Teams T ON T.ID = B.ID_team 
INNER JOIN Role R ON R.ID = B.ID_role
GROUP BY B.ID_user

---

编辑:

无论 Bridge 表中是否存在相应的记录，为了让所有用户都得到结果:

SELECT 
U.ID,
U.Name,
GROUP_CONCAT(CONCAT(T.NameTeam,':',R.Role)) AS 'Team and Role'
FROM Users U 
LEFT JOIN Bridge B ON U.ID = B.ID_user 
LEFT JOIN Teams T ON T.ID = B.ID_team 
LEFT JOIN Role R ON R.ID = B.ID_role
GROUP BY U.ID;

---

SEE DEMO