文档中心

排名函数 ​

排名函数就是对开窗后查询的结果进行排名，其中rank、dense_rank、row_number、ntile均为排名函数和开窗配合使用返回特定的排名结果。

  • 当使用rank函数出现相同排名时，相同的排名使用相同的排名号且后续排名号依次递增跳过，例如a和b并列排名号为1下一个数据c排名号则为3，rank函数不支持只包括分区的情况。
  • 当使用dense_rank函数出现相同排名时，相同排名使用相同的排名号且后续排名号依次递增不跳过，例如a和b并列排名号为1下一个数据c的排名号则为2，dense_rank函数不支持只包括分区的情况。
  • row_number函数为每一组的行按顺序生成一个唯一的排名号，排名号从1开始，依次递增，row_number函数不支持只包括分区的情况。
  • ntile按照指定的数目将数据进行分组，并为每一组生成一个序号，将每组的序号分配给每一行，ntile函数不支持只包括分区的情况。
  • rank、dense_rank、row_number为无参函数，ntile为单参数函数，其中参数表示需要分配的组数。

示例 ​

  • 示例1
    rank排名

    sql
    CREATE TABLE sal_info(id INT IDENTITY(1,1),name VARCHAR,deptno INT,sal NUMERIC(10,2));
INSERT INTO sal_info VALUES(DEFAULT,'张三',10,5000);
INSERT INTO sal_info VALUES(DEFAULT,'李四',10,5500);
INSERT INTO sal_info VALUES(DEFAULT,'王五',10,4500);
INSERT INTO sal_info VALUES(DEFAULT,'赵六',10,4800);
INSERT INTO sal_info VALUES(DEFAULT,'陈七',10,5500);
INSERT INTO sal_info VALUES(DEFAULT,'刘八',20,3000);
INSERT INTO sal_info VALUES(DEFAULT,'李九',20,3500);
INSERT INTO sal_info VALUES(DEFAULT,'周十',20,3800);
INSERT INTO sal_info VALUES(DEFAULT,'张一',20,2300);
INSERT INTO sal_info VALUES(DEFAULT,'李二',20,3500);
INSERT INTO sal_info VALUES(DEFAULT,'吴二',20,3800);
INSERT INTO sal_info VALUES(DEFAULT,'张二',20,3800);
INSERT INTO sal_info VALUES(DEFAULT,'刘二',20,4000);
INSERT INTO sal_info VALUES(DEFAULT,'周二',20,4300);

SQL> SELECT *,RANK() OVER(PARTITION BY deptno ORDER BY sal) rk FROM sal_info ORDER BY deptno,rk;

ID | NAME | DEPTNO | SAL | RK | 
------------------------------------------------------------------------------
3 | 王五| 10 | 4500| 1 |
4 | 赵六| 10 | 4800| 2 |
1 | 张三| 10 | 5000| 3 |
5 | 陈七| 10 | 5500| 4 |
2 | 李四| 10 | 5500| 4 |
9 | 张一| 20 | 2300| 1 |
6 | 刘八| 20 | 3000| 2 |
10 | 李二| 20 | 3500| 3 |
7 | 李九| 20 | 3500| 3 |
11 | 吴二| 20 | 3800| 5 |
8 | 周十| 20 | 3800| 5 |
12 | 张二| 20 | 3800| 5 |
13 | 刘二| 20 | 4000| 8 |
14 | 周二| 20 | 4300| 9 |

  • 示例2
    dense_rank排名

    sql
     SQL> SELECT *,DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) dr FROM sal_info ORDER BY deptno,dr;

ID | NAME | DEPTNO | SAL | DR | 
------------------------------------------------------------------------------
3 | 王五| 10 | 4500| 1 |
4 | 赵六| 10 | 4800| 2 |
1 | 张三| 10 | 5000| 3 |
5 | 陈七| 10 | 5500| 4 |
2 | 李四| 10 | 5500| 4 |
9 | 张一| 20 | 2300| 1 |
6 | 刘八| 20 | 3000| 2 |
10 | 李二| 20 | 3500| 3 |
7 | 李九| 20 | 3500| 3 |
11 | 吴二| 20 | 3800| 4 |
8 | 周十| 20 | 3800| 4 |
12 | 张二| 20 | 3800| 4 |
13 | 刘二| 20 | 4000| 5 |
14 | 周二| 20 | 4300| 6 |

  • 示例3
    row_number排名

    sql
     SQL> SELECT *,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) rn FROM sal_info ORDER BY deptno,rn;

ID | NAME | DEPTNO | SAL | RN | 
------------------------------------------------------------------------------
3 | 王五| 10 | 4500| 1 |
4 | 赵六| 10 | 4800| 2 |
1 | 张三| 10 | 5000| 3 |
2 | 李四| 10 | 5500| 4 |
5 | 陈七| 10 | 5500| 5 |
9 | 张一| 20 | 2300| 1 |
6 | 刘八| 20 | 3000| 2 |
7 | 李九| 20 | 3500| 3 |
10 | 李二| 20 | 3500| 4 |
8 | 周十| 20 | 3800| 5 |
11 | 吴二| 20 | 3800| 6 |
12 | 张二| 20 | 3800| 7 |
13 | 刘二| 20 | 4000| 8 |
14 | 周二| 20 | 4300| 9 |

  • 示例4
    ntile排名

    sql
     SQL> SELECT *,NTILE(5) OVER(PARTITION BY deptno ORDER BY sal) nt FROM sal_info ORDER BY deptno,nt;

ID | NAME | DEPTNO | SAL | NT | 
------------------------------------------------------------------------------
3 | 王五| 10 | 4500| 1 |
4 | 赵六| 10 | 4800| 2 |
1 | 张三| 10 | 5000| 3 |
2 | 李四| 10 | 5500| 4 |
5 | 陈七| 10 | 5500| 5 |
9 | 张一| 20 | 2300| 1 |
6 | 刘八| 20 | 3000| 1 |
10 | 李二| 20 | 3500| 2 |
7 | 李九| 20 | 3500| 2 |
8 | 周十| 20 | 3800| 3 |
11 | 吴二| 20 | 3800| 3 |
13 | 刘二| 20 | 4000| 4 |
12 | 张二| 20 | 3800| 4 |
14 | 周二| 20 | 4300| 5 |