select * from emp a
where not exists(select 1 from role b where not exists(select 1 from emp where EmpValue = b.roleValue and EmpID = a.EmpID) )
好象有点乱,楼主试一下吧。
where not exists(select 1 from role b where not exists(select 1 from emp where EmpValue = b.roleValue and EmpID = a.EmpID) )
好象有点乱,楼主试一下吧。
from Emp aa
where not exists(select 1 from RoleVaue
where RoleValue not in(select EmpValue from Emp where EmpID=aa.EmpID)
)
insert role values('共产党员')
insert role values('大学毕业')
gocreate table emp(EmpID varchar(5),EmpValue varchar(10))
insert into emp select 'A0001','共产党员'
union all select 'A0001','大学毕业'
union all select 'A0001','女'
union all select 'A0002','共产党员'
union all select 'A0002','中学毕业'
go
select * from emp a
where not exists(select 1 from role b where not exists(select 1 from emp where EmpValue = b.roleValue and EmpID = a.EmpID) )/*
EmpID EmpValue
----- ----------
A0001 共产党员
A0001 大学毕业
A0001 女(所影响的行数为 3 行)*/select distinct EmpID from emp a
where not exists(select 1 from role b where not exists(select 1 from emp where EmpValue = b.roleValue and EmpID = a.EmpID) )
/*
EmpID
-----
A0001(所影响的行数为 1 行)
*/