如何找到在MySQL或Redshift中带来另一个依赖的依赖

Question

有两个表:

dependency_permission表:

tbody> <<tr>4

id	dependency_permission_id
2	1
	2

您可以左连接'user_permission'表与'dependency_permission'得到'dependency_permission_id'每个用户'permission_id'，然后使用NOT EXISTS算子检查'dependency_permission_id'每个用户'permission_id'是否存在

with user_dependency_permission as
(
select U.user_id, U.permission_id, D.dependency_permission_id
from user_permission U left join dependency_permission D
on U.permission_id = D.id 
)
select user_id, permission_id /* if you want to select only user_ids use distinct user_id*/
from user_dependency_permission T
where not exists(
select 1 from user_dependency_permission D
where T.user_id=D.user_id and 
D.permission_id=T.dependency_permission_id
)
and T.dependency_permission_id is not null

查看MySQL的演示