SQL中ROW_NUMBER()/RANK() /DENSE_RANK() OVER函数的基本用法

一、ROW_NUMBER()的用法

 

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

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

Row_Num    colum

     1              2200

     2              2150

     3             1780

     4             1125

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

实例1：

初始化数据

1.  
   create table employer (employerid int ,deptid int ,salary decimal(8,1))
2.  
    
3.  
   insert into employer values(1,1,15000.0)
4.  
    
5.  
   insert into employer values(2,1,10000.0)
6.  
    
7.  
   insert into employer values(3,2,19000.0)
8.  
    
9.  
   insert into employer values(4,2,21000.0)
10.  
     
11.  
    insert into employer values(5,3,14500.0)
12.  
     
13.  
    insert into employer values(6,3,10000.0)
14.  
     
15.  
    insert into employer values(7,3,44500.0)
16.  
     
17.  
    insert into employer values(8,4,22500.0)
18.  
     
19.  
    insert into employer values(9,4,35500.0)
20.  
     
21.  
    insert into employer values(10,4,35500.0)
22.  
     
23.  
    insert into employer values(11,4,36000.0)
24.  
     
25.  
    insert into employer values(12,4,36000.0)

 

数据显示为

employerid       deptid      salary
----------- ----------- ---------------------------------------
1                         1          15000.0

2                         1          10000.0

3                         2          19000.0

4                         2          21000.0

5                         3          14500.0

6                         3          10000.0

7                         3          44500.0

8                         4          22500.0

9                         4          35500.0

10                       4          35500.0

11                       4          36000.0

12                       4          36000.0

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

预期结果：

employerid       deptid      salary              Leve  
----------- ----------- ---------------------------------------
1                         1          15000.0               1

2                         1          10000.0               2

4                         2          21000.0               1 

3                         2          19000.0               2

7                         3          44500.0               1

5                         3          14500.0               2

6                         3          10000.0               3

11                       4          36000.0               1

12                       4          36000.0               2

9                         4          35500.0               3

10                       4          35500.0               4

8                         4          22500.0               5

SQL脚本：

SELECT *, ROW_NUMBER() OVER (PARTITION BY deptid ORDER BY salary desc) Leve FROM employeer

 

实例2：

初始化数据

1.  
   create table tb_EmployerSign (SignId int ,EmployerId int ,SignDate datetime)-- 创建员工签到表
2.  
    
3.  
   insert into tb_EmployerSign values(1,1,'2014-09-15 18:21:38.130' )
4.  
    
5.  
   insert into tb_EmployerSign values(2,2,'2014-09-16 18:21:38.130' )
6.  
    
7.  
   insert into tb_EmployerSign values(3,3,'2014-09-14 18:21:38.130' )
8.  
    
9.  
   insert into tb_EmployerSign values(4,4,'2014-09-16 18:21:38.130' )
10.  
     
11.  
    insert into tb_EmployerSign values(5,1,'2014-09-17 18:21:38.130' )
12.  
     
13.  
    insert into tb_EmployerSign values(6,2,'2014-09-17 19:21:38.130' )
14.  
     
15.  
    insert into tb_EmployerSign values(7,3,'2014-09-19 18:21:38.130' )
16.  
     
17.  
    insert into tb_EmployerSign values(8,4,'2014-09-20 18:21:38.130' )

数据显示为

 

SignId       EmployerId              SignDate
----------- ----------- -------------------------------------------
1                      1            2014-09-15 18:21:38.130

2                      2            2014-09-16 18:21:38.130

3                      3            2014-09-14 18:21:38.130

4                      4            2014-09-16 18:21:38.130

5                      1            2014-09-17 18:21:38.130

6                      2            2014-09-17 19:21:38.130

7                      3            2014-09-19 18:21:38.130

8                      4            2014-09-20 18:21:38.130

需求：查询三天内没有签到的员工最后一次签到的信息

假如今天是2014-09-21 则预期结果：

    SignId             EmployerId             SignDate                        OutDateNumb
-------------------------------------------------------------------------------------------------------
       5                           1          2014-09-17 18:21:38.130                   4

       6                           2          2014-09-17 19:21:38.130                   4

SQL脚本：

1.  
   select SignId,EmployerId,SignDate,datediff(dd,SignDate,getdate()) as OutDateNumb
2.  
    
3.  
   from (select *,ROW_NUMBER() over(PARTITION by EmployerId order by signId DESC) numb from EmployerSign) tb
4.  
    
5.  
   where tb.numb=1 and datediff(dd,SignDate,getdate())>3<span style="font-size:14px;"><strong>
6.  
   </strong></span>

 二、RANK()的用法

 

语法：RANK() OVER (PARTITION BY COL1 ORDER BY COL2) 

RANK()的用法和ROW_NUMBER()类似，只不过RANK()是跳跃排序，有两个第三名时接下来就是第五名（同样是在各个分组内）．

例如执行如下SQL语句之后实例1中的数据显示结果如下：

SELECT *, RANK() OVER (PARTITION BY deptid ORDER BY salary desc) Leve FROM employer

结果：

employerid       deptid      salary              Leve  
----------- ----------- ---------------------------------------
1                         1          15000.0               1

2                         1          10000.0               2

4                         2          21000.0               1

3                         2          19000.0               2

7                         3          44500.0               1

5                         3          14500.0               2

6                         3          10000.0               3

11                       4          36000.0               1

12                       4          36000.0               1

9                         4          35500.0               3

10                       4          35500.0               3

8                         4          22500.0               5

 

三、DENSE_RANK()的用法

 

语法：DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)

DENSE_RANK()的用法和ROW_NUMBER()类似，只不过DENSE_RANK()是连续排序，有两个第二名时仍然跟着第三名(同样在各个分组内)。

例如执行如下SQL语句后实例1中的数据显示如下：

SELECT *, DENSE_RANK() OVER (PARTITION BY deptid ORDER BY salary desc) Leve FROM employee

结果：

employerid       deptid      salary              Leve  
----------- ----------- ---------------------------------------
1                         1          15000.0               1

2                         1          10000.0               2

4                         2          21000.0               1

3                         2          19000.0               2

7                         3          44500.0               1

5                         3          14500.0               2

6                         3          10000.0               3

11                       4          36000.0               1

12                       4          36000.0               1

9                         4          35500.0               2

10                       4          35500.0               2

8                         4          22500.0               3