php - 将多条记录加入一行？

Question

这是我的数据库结构:

Users {User ID 1}
Roles {For example 2 roles: 1, 2}
User_roles {User ID 1: has Role 1, User ID 1: Has role 2}

我如何使用 join 来获得这两个角色，因为这个查询复制了用户:

SELECT * FROM `users` LEFT JOIN `user_roles` ON (`users`.`id` = `user_roles`.`user_id`)

我想输出一个看起来像这样的表格:

User ID | Has Role 1 | Has Role 2
--------|------------|-----------
1       |    yes     |    yes

Answer 1

您可以按用户 ID 分组。

SELECT 
    `users`.`id`, 
     IF(SUM(`roles`.`id` = 1), 'yes', 'no') AS `Has Role 1`,
     IF(SUM(`roles`.`id` = 2), 'yes', 'no') AS `Has Role 2`
FROM `users` 
LEFT JOIN `user_roles` ON `users`.`id` = `user_roles`.`user_id`
LEFT JOIN `roles` ON `roles`.role_id = `user_roles`.`role_id`
GROUP BY `users`.`id`

如果你只是使用角色的 pk，那么你可以省略 LEFT JOIN roles by

SELECT 
    `users`.`id`, 
     IF(SUM(`user_roles`.`role_id` = 1), 'yes', 'no') AS `Has Role 1`,
     IF(SUM(`user_roles`.`role_id` = 2), 'yes', 'no') AS `Has Role 2`
FROM `users` 
LEFT JOIN `user_roles` ON `users`.`id` = `user_roles`.`user_id`
GROUP BY `users`.`id`