文档中心

排名函数 ​

功能描述 ​

排名函数就是对开窗后查询的结果进行排名，其中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为单参数函数，其中参数表示需要分配的组数。

示例 ​

RANK排名 ​

sql
SQL> CREATE TABLE sal_info(id INT IDENTITY(1,1),name VARCHAR,deptno INT,sal NUMERIC(10,2));

SQL> INSERT INTO sal_info VALUES(DEFAULT,'张三',10,5000);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'李四',10,5500);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'王五',10,4500);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'赵六',10,4800);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'陈七',10,5500);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'刘八',20,3000);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'李九',20,3500);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'周十',20,3800);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'张一',20,2300);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'李二',20,3500);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'吴二',20,3800);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'张二',20,3800);
SQL> INSERT INTO sal_info VALUES(DEFAULT,'刘二',20,4000);
SQL> 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 |

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 |

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 |

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 |