mysql - 根据外部条件从联接中仅选择一行

Question

主要思想

• 用户 hasMany 角色(数据存储在表中:users、roles、user_role)<
• 我想知道用户是管理员还是客户
• 然后这些数据将join到不同的结果

我在做什么

SELECT
  `users`.`id`,
  `users`.`name`,
  `roles`.`display_name`
FROM `users`
  JOIN role_user ON users.id = role_user.user_id
  JOIN roles ON role_user.role_id = roles.id

为什么错了

因为这是我得到的结果

id       Name    Display name

1       admin    Admin
1       admin    Client
2       admin2   Admin
2       admin2   Client
3       client   Client
7       test     Admin
7       test     Client

我想要什么

id       Name   Display name

1       admin   Admin
2       admin2  Admin
3       client  Client
7       test    Admin

我是如何工作的

• 以某种方式使用聚合
• 以某种方式使用案例
• 以某种方式加入数据的子集

谢谢你的想法!

[更新] 这是一个 sqlFiddle描述问题。

Answer 1

此解决方案应该适合您:

select * from users
where users.id in (
select users.id FROM users
  JOIN role_user ON (users.id = role_user.user_id)
  JOIN roles ON (role_user.role_id = roles.id)
  where roles.display_name like 'admin'
)
Union
select * from users
where users.id not in (
select users.id FROM users
  JOIN role_user ON (users.id = role_user.user_id)
  JOIN roles ON (role_user.role_id = roles.id)
  where roles.display_name like 'admin'
);

在这里 fiddle :http://sqlfiddle.com/#!9/0fabba/42

我建议为子查询创建一个 View ，但我不能在 fiddle 上做到这一点。

使用 View 的例子:

Create or replace view admin_list as
  select users.id FROM users
  JOIN role_user ON (users.id = role_user.user_id)
  JOIN roles ON (role_user.role_id = roles.id)
  where roles.display_name like 'admin';

那么查询会更紧凑:

select * from users
where users.id in (
  select users.id FROM admin_list
)
Union
select * from users
where users.id not in (
  select users.id FROM admin_list
);