代码之家 › 专栏 › 技术社区 › user1578872

MySQL-将行转换为逗号分隔的值

crosstab pivot-table sql mysql

user1578872 · 技术社区 · 4 年前

我有三张桌子。

1. user
2. user_role
3. user_role_mapping

样品记录,

用户

+--------+----------+---------------+
| userid | username | email         |
+--------+----------+---------------+
|      1 | user1    |user1@test.com |
|      2 | user2    |user2@test.com |
+--------+----------+---------------+

user_role

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

用户_角色_映射

+--------+------+
| 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    |user1@test.com | 1    |   1     |  1       |  1    |
+--------+----------+---------------+------------------------------------

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

1 回复 | 直到 4 年前

user1578872 4 年前

对于逗号分隔,您可以使用 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 = ?;

推荐文章

B.Poe · 在熊猫数据透视表中显示映射标签而不是代码

7 年前

Greg Viers Russ Ebbing · 如何使用VBA折叠只有一个子项的数据透视项?

7 年前

brendxn · 如何在数据帧中透视一个包含字符串的列?[副本]

7 年前

AmirA · Excel VBA。从Personal创建数据透视表。xlsb公司

7 年前

00__00__00 · dataframe到基于两列的dataframe列表

7 年前

Anif Maulana · 在postgres中从两个不同的列中划行

7 年前

Cindy · 为困难的数据集python创建数据透视表

7 年前

Newskooler · 如何创建熊猫数据透视表

7 年前

K.R. · Excel数据透视表日期筛选器[关闭]

7 年前

P. Prunesquallor · pandas pivot\u表:aggfunc参数值和引号

7 年前