问题:我有一个可以按预期工作的GROUP_CONCAT查询，除了我希望将concat作为一个合并的答案，而不是原始ID字段。
当前查询:
SELECT user.user_id, user.user, GROUP_CONCAT(user_roles.roleID separator ', ') roles
FROM user
JOIN user_roles ON user.user_ID = user_roles.user_ID
GROUP BY users.user_ID, users.user
提供结果:
+----------+---------+----------------------------+
| user_ID | user | roles |
+----------+---------+----------------------------+
| 1 | Smith | 1, 3 |
+----------+---------+----------------------------+
| 2 | Jones | 1, 2, 3 |
+----------+---------+----------------------------+
所需结果:
+----------+---------+----------------------------+
| user_ID | user | roles |
+----------+---------+----------------------------+
| 1 | Smith | Admin, Other |
+----------+---------+----------------------------+
| 2 | Jones | Admin, Staff, Other |
+----------+---------+----------------------------+
用户表:
+----------+---------+
| user_ID | user |
+----------+---------+
| 1 | Smith |
+----------+---------+
| 2 | Jones |
+----------+---------+
* users_roles表:*
+----------+---------+
| user_ID | role_ID |
+----------+---------+
| 1 | 1 |
+----------+---------+
| 2 | 1 |
+----------+---------+
| 2 | 2 |
+----------+---------+
| 2 | 3 |
+----------+---------+
| 1 | 3 |
+----------+---------+
角色表:
+----------+-----------+
| role_ID | role_name |
+----------+-----------+
| 1 | Admin |
+----------+-----------+
| 2 | Staff |
+----------+-----------+
| 3 | Other |
+----------+-----------+
最佳答案
尝试以下查询
SELECT user.user_id, user.user, GROUP_CONCAT(roles.role_name separator ', ') roles
FROM user
JOIN user_roles ON user.user_ID = user_roles.user_ID
JOIN roles ON user_roles.role_ID= user_roles.role_ID
GROUP BY users.user_ID, users.user
关于mysql - 将内部联接添加到mySQL GROUP_CONCAT语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10084352/