MySQL -透视行到逗号分隔的值

Question

我有3张表

1. user
2. user_role
3. user_role_mapping

样本记录,

用户

+--------+----------+---------------+
| userid | username | email         |
+--------+----------+---------------+
|      1 | user1    |[e-mail] |
|      2 | user2    |[e-mail] |
+--------+----------+---------------+

user_role

+--------+----------+
| roleid | rolename |
+--------+----------+
|      1 | user     |
|      2 | manager  |
|      3 | director |
|      4 | admin    |
+--------+----------+

User_role_mapping

+--------+------+
| roleid |userid|
+--------+------+
|      1 | 1    |
|      2 | 1    |
|      3 | 1    |
+--------+------+

查询

select u.userid, u.username, u.email,
count(case when ur.rolename = 'user' THEN 1 END) user,
count(case when ur.rolename = 'manager' THEN 1 END) manager,
count(case when ur.rolename = 'director' THEN 1 END) director,
count(case when ur.rolename = 'admin' THEN 1 END) admin
from user_role ur
left join userrole_mapping urm
on ur.roleid = urm.roleid
left join user u
on urm.userid = u.userid
group by u.userid, u.username, u.email

结果: -

+--------+----------+---------------+------|---------|----------|-------|
| userid | username | email         | user | manager | director | admin |
+--------+----------+---------------+------------------------------------
|      1 | user1    |[e-mail] | 1    |   1     |  1       |  1    |
+--------+----------+---------------+------------------------------------

在这里，我在查询中硬编码角色，新角色可以在将来添加，我不想做代码更改。有没有更好的办法?我对结果中角色的逗号分隔结果很好。

Answer 1

对于逗号分隔，可以使用group_concat:

select u.user_id, u.user_name, u.email,
Group_concat(ur.role_name) roles
from user u
left join user_role_mapping urm
on urm.user_id = u.user_id
left join user_role ur
on ur.role_id = urm.role_id
where u.user_id = ?
group by u.user_id;

另一个更好的方法是单独查询角色:

select u.user_id, u.user_name, u.email, ur.role_name
from user u
left join user_role_mapping urm
on urm.user_id = u.user_id
left join user_role ur
on ur.role_id = urm.role_id
where u.user_id = ?;