当前位置:数据库 > MySQL >>

sql 求两表交集两种方法

sql 求两表交集两种方法

dept表

id deptid
1 20
2 20
3 20

user表

id userid
1 33
2 34
3 34

方法一

select distinct userid
from user u
where id in (select id from dept where deptid=20)
and not exists (select 1 from user where id in (select id from dept deptid=20) and userid<>u.userid)

方法二 join

select distinct userid
from user u inner join dept d on u.id=d.id
where d.deptid=20
and not exists (select 1 from user inner join dept on user.id=dept.id where userid<>u.userid)

补充:数据库,mysql教程
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,