PARTITION ROW_NUMBER()的运用

 

****************************************
下面sql说明：从1123业务id中检索出各地市投递站的信息（5条）
PARTITION ROW_NUMBER()的运用
****************************************
SELECT *
  FROM (SELECT A.DEPT_CODE,
               B.*,
               ROW_NUMBER() OVER(PARTITION BY A.DEPT_CODE ORDER BY B.ADDR_ID) RN
          FROM DMC.ORG_DEPARTMENT A, T_CUST_ADDR B
         WHERE B.STATION = A.DEPT_CODE
           AND A.DEPT_TYPE = 20
           AND B.STATION IS NOT NULL
           AND B.BUSINESS_ID = 1123)
 WHERE RN <= 5