having

 select EMPNO,count(EMPNO) empcount from
  (SELECT   E.EMPNO,
           E.AD_ACCOUNT,
           E.NAME AS USERNAME,
           D.DEPTNO AS UNITID,
           D.NAME AS UNITNAME
    FROM   [e-mail] E, [e-mail] D
   WHERE       E.ORGANIZATION_CODE = D.DEPTNO)
group by EMPNO
having count(EMPNO) >1

 

結果：

EMPNO EMPCOUNT

E1370 4
K6991 4
50078 3
41384 5
41315 3
44021 13

 

假如你不用having的話，用where也可以，但會麻煩一些：

select * from (select EMPNO,count(EMPNO) empcount from
  (SELECT   E.EMPNO,
           E.AD_ACCOUNT,
           E.NAME AS USERNAME,
           D.DEPTNO AS UNITID,
           D.NAME AS UNITNAME
    FROM   [e-mail] E, [e-mail] D
   WHERE       E.ORGANIZATION_CODE = D.DEPTNO)
group by EMPNO)
where empcount>1