select unitno,empid from emp group by unitno,empid having count(1)>1
--unitno 为单位,默认为1单位,如下: select empid from emp where unitno= 1 group by empid having count(empid) >1
select empid from emp a where exists (select 1 from emp where unitno=a.unitno and empid=a.empid)select empid from(select unitno,empid,count(*) from emp group by unitno,empid having count(*)>=2) a
能不能再加上empname ,这样查只能查出一条结果,我想把所有的结果都查出来,好修改
select a.empid, a.unitno, a.[name] from emp as a, emp as b where a.empid = b.empid and a.unitno = b.unitno and a.[name] <> b.[name]
--unitno 为单位,默认为1单位,如下:
select empid from emp where unitno= 1
group by empid having count(empid) >1
select empid from emp a where exists (select 1 from emp where unitno=a.unitno and empid=a.empid)select empid from(select unitno,empid,count(*) from emp
group by unitno,empid
having count(*)>=2) a
where a.empid = b.empid
and a.unitno = b.unitno
and a.[name] <> b.[name]