ORACLE 的OVER PARTITION BY

        可以认为以DEPARTMENT_NAME_CN分组，然后去order by 取出第一个的数字。

        SELECT DEPTNAME,
                  DEPARTMENT_NAME_CN
                FROM
                  (SELECT (ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_NAME_CN ORDER BY DEPTNAME)) AS RN,
                    DEPTNAME,
                    DEPARTMENT_NAME_CN
                  from HR_DEPT_SUNYI
                  where CPNY_ID='C14'
                     and    DEPT_DISTINGUISH_NAME='121985'
                              and ACTIVITY=1
                                ORDER BY ORDER_BY
                  )
                WHERE RN<2;

 2015-11-13

如果去掉RN<2查询的个数是17个，加上这个就14个。说明DEPARTMENT_NAME_CN有重复的，有重复的会只出来一个。