时间戳

首页 > 解决方案 > MySQL在列表中但不在临时表中

mysql - MySQL在列表中但不在临时表中

问题描述

我正在考虑以下两张表

|------------|  |-----------|
| user_roles |  |   roles   | 
|============|  |===========|
| user_id    |  | role_id   |
| role_id    |  | code_name |
|------------|  |-----------|

我想获取给定 user_id 列表中 user_id 的所有 user_roles。但我想排除所有具有 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)


但是一定有更好的方法吗？！

标签： mysqlsqljoinselect

解决方案

您可以使用窗口函数 - 如果您正在运行 MySQL 8.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

在早期版本中，一种方法是not exists：

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'
)

推荐阅读