ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)的用法

用法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

 

原始资料地址:http://jingyan.baidu.com/article/9989c74604a644f648ecfef3.html

 

1.简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的每条xlh记录返回一个序号。

2.row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)。

3.eg:

   create table employee (empid int ,deptid int ,salary decimal(10,2))

   insert into employee values(1,10,5500.00);

   insert into employee values(2,10,4500.00);

   insert into employee values(3,20,1900.00);

   insert into employee values(4,20,4800.00);

   insert into employee values(5,40,6500.00);

   insert into employee values(6,40,14500.00);

   insert into employee values(7,40,44500.00);

   insert into employee values(8,50,6500.00);

   insert into employee values(9,50,7500.00);

   需求：根据部门分组，显示每个部门的工资等级

   sql:

   select *, row_number() over (partition by deptid order by salary desc) rank from employee

 

4.项目中运用   

   SELECT T.C_CUSTTYPE,
               T.C_CUSTNAME,
               T.C_IDENTITYTYPE,
               T.C_IDENTITYNO,
               T.F_LASTSHARES,
               CHILD.PRDT_NAME,
               CHILD.PRDT_CODE,
   (
       CASE
       WHEN PRDT.HAVE_CHILD_FLAG = 0 THEN
             PRDT.PRDT_CONVERT_CODE
       ELSE
             CHILD.PRDT_CODE
       END
   ) PRDT_CONVERT_CODE,
   ROW_NUMBER() OVER( PARTITION BY T.C_FUNDACCO, T.C_AGENCYNO, T.C_TRADEACCO, T.C_FUNDCODE, T.C_SHARETYPE ORDER BY        T.D_CDATE DESC, T.L_SERIALNO DESC) RN
   FROM V_TA_SHARECURRENT T,
            POS_PRDT_CHILD CHILD,
            POS_PRDT PRDT
   WHERE T.C_FUNDCODE = CHILD.PRDT_CODE
            AND CHILD.PARENT_PRDT_CODE = PRDT.PRDT_CODE