我正在考虑以下两个表格
|------------| |-----------|
| user_roles | | roles |
|============| |===========|
| user_id | | role_id |
| role_id | | code_name |
|------------| |-----------|
我想在给定的user_id列表中获取所有user_roles，其中user_id。 但我希望排除所有具有code_name='special_role'角色的用户。
最好的办法是什么？
作为示例，假设我有以下内容:
user_roles: roles:
| user_id | role_id | | role_id | code_name |
|=========|=========| |=========|==============|
| 1 | 1 | | 1 | special_role |
| 1 | 2 | | 2 | another_role |
| 2 | 2 | |---------|--------------|
| 3 | 2 |
|---------|---------|
我的想法是使用临时表，例如:
create temporary table if not exists all_user_ids as (
select ur.user_id as user_id, ur.role_id as role_id
from user_roles ur
where ur.user_id in (1,2,3)
);
create temporary table if not exists special_user_ids as (
select aui.user_id as user_id
from all_user_ids aui
join roles r on r.role_id = aui.role_id
where r.code_name = 'special_role'
);
create temporary table if not exists non_special_user_ids as (
select aui.user_id as user_id
from all_user_ids aui
where aui.user_id not in (special_user_ids.user_id)
);
然后为了我的最终结果，我可以做:
select ur.user_id, ur.role_id
from user_roles ur
where ur.user_id in (non_special_user_ids.user_id)
但一定有更好的方法？！
您可以使用窗口函数--如果您正在运行MySQL8.0:
select *
from (
select ur.*, r.code_name, max(r.code_name = 'special_role') over(partition by user_id) has_special_role
from user_roles ur
inner join roles r on r.role_id = ur.role_id
) t
where has_special_role = 0
在早期版本中，一种方法是不存在:
select ur.*
from user_roles ur
where not exists (
select 1
from user_roles ur1
inner join roles r1 on r1.role_id = ur1.role_id
where ur1.user_id = ur.user_id and r1.code_name = 'special_role'
)
就加入吧。 这应该是相当快的假设你有钥匙设置。
SELECT * FROM user_roles JOIN role ON user_roles.role_id = role.role_id
WHERE user_roles.user_id IN(1,2,3 ...) AND role.code_name != "special_role"
误解了你的要求。 如果您不希望有任何特殊角色的用户:
SELECT * FROM user_roles WHERE user_id NOT IN(
SELECT user_id FROM user_roles JOIN role ON user_role.role_id = role.role_id
WHERE role.role_code = 'special_role')
AND user_id IN (1, 2, 3 ...)
对于以下两种情况，请使用in和not in:
select *
from user_roles
where user_id in (<list of usr_ids>)
and user_id not in (
select user_id from user_roles
where role_id = (select role_id from roles where code_name = 'special_role')
)
请参阅演示。
