having

中辽普坦

于 2011-06-07 14:57:00 发布

阅读量665

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/byondocean/article/details/6529642

版权

Oracle 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

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   MHR_EMPLOYEE_all@MICERP.US.ORACLE.COM E, msl_oa_departments@MICERP.US.ORACLE.COM 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   MHR_EMPLOYEE_all@MICERP.US.ORACLE.COM E, msl_oa_departments@MICERP.US.ORACLE.COM D
   WHERE       E.ORGANIZATION_CODE = D.DEPTNO)
group by EMPNO)
where empcount>1