mysql - MySQL多对多查询

Question

我有两个表:用户 [id, email] 和角色 [id, name] 以及多对多连接表 user_has_roles [user_id, role_id]。我正在尝试获取显示哪个用户具有特定角色的表，如果没有则为 null(或 0)，类似于以下内容:

            | role 1 | role 2 | role 3
---------------------------------------
user1@email | 1      | null   | 1
user2@email | null   | null   | 1
---------------------------------------

非常感谢对此的任何帮助。

Answer 1

select u.email, 
       case when sum(r.name = 'role 1') > 0 then 1 else null end as `role 1`,
       case when sum(r.name = 'role 2') > 0 then 1 else null end as `role 2`,
       case when sum(r.name = 'role 3') > 0 then 1 else null end as `role 3`
from users u
left join user_has_roles ur on ur.user_id = u.id
left join roles r on ur.role_id = r.id
group by u.email