sql 求两表交集两种方法
sql 求两表交集两种方法
dept表
id deptid
1 20
2 20
3 20user表
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教程