select emid from hrinfo a where exists (select * from cerid in('1001','1002') and a.emid =emid )
to caiyunxia,你这样查出来得还是有1001和1002证书得人,而不是同时具有1001和1002证书得人
SELECT * FROM hrinfo WHERE EXISTS (select 1 from cerinfo where cerid = '1001' and emid = hrinfo.empid) and EXISTS (select 1 from cerinfo where cerid = '1002' and emid = hrinfo.empid)
create table hrinfo(emid varchar(10) not null,name varchar(20)) create table cerinfo(emid varchar(10) not null,cerid varchar(10))select a.emid,a.name from hrinfo a left join cerinfo b on a.emid=b.emid where b.cerid='1001' or b.cerid='1002'
to playyuer,从效率上看,跟我得实现方法差不多。难道没有更简单得方法了嘛?
select empid ,count(cerid) from cerid group by empid having count(cerid)>=2;
下面的这个效率比你的快 select emid from (select * from cerinfo where cerid='1001') as temp where emid in (select emid from cerinfo where cerid='1002')
select * from hrinfo inner join cerinfo on cerinfo.empid=hrinfo.empid where creid in ('10001', '10002')
(SELECT emid FROM cerinfo WHERE cerid = '1001') UNIOIN (SELECT emid FROM cerinfo WHERE cerid = '2002')
select emid from cerinfo where cerid in('1001',1002)
where exists (select * from cerid in('1001','1002') and a.emid =emid )
FROM hrinfo
WHERE EXISTS (select 1 from cerinfo where cerid = '1001' and emid = hrinfo.empid)
and EXISTS (select 1 from cerinfo where cerid = '1002' and emid = hrinfo.empid)
create table hrinfo(emid varchar(10) not null,name varchar(20))
create table cerinfo(emid varchar(10) not null,cerid varchar(10))select a.emid,a.name
from hrinfo a left join cerinfo b on a.emid=b.emid
where b.cerid='1001' or b.cerid='1002'
select emid from (select * from cerinfo where cerid='1001') as temp where emid in (select emid from cerinfo where cerid='1002')
(SELECT emid FROM cerinfo WHERE cerid = '2002')