sql中ROW_NUMBER()和RANK()和DENSE_RANK()函数

ROW_NUMBER()函数

假设有如下的 employees 表：

+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | Alice |   5000 |
|  2 | Bob   |   6000 |
|  3 | Carol |   5000 |
|  4 | Dave  |   4000 |
+----+-------+--------+

如果使用 ROW_NUMBER() 函数计算排名，那么查询语句可以写成：

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

执行结果如下：

+-------+--------+--------+
| name  | salary | row_num|
+-------+--------+--------+
| Bob   |   6000 |      1 |
| Alice |   5000 |      2 |
| Carol |   5000 |      3 |
| Dave  |   4000 |      4 |
+-------+--------+--------+

RANK()

如果希望在相同薪水的情况下产生连续的排名，可以使用 RANK() 函数

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;

-- 执行结果如下
+-------+--------+---------+
| name  | salary | rank_num|
+-------+--------+---------+
| Bob   |   6000 |       1 |
| Alice |   5000 |       2 |
| Carol |   5000 |       2 |
| Dave  |   4000 |       4 |
+-------+--------+---------+

DENSE_RANK()

如果希望在相同薪水的情况下返回相同值的并列排名，可以使用 DENSE_RANK() 函数

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- 执行结果如下
+-------+--------+---------------+
| name  | salary | dense_rank_num|
+-------+--------+---------------+
| Bob   |   6000 |             1 |
| Alice |   5000 |             2 |
| Carol |   5000 |             2 |
| Dave  |   4000 |             3 |
+-------+--------+---------------+

总结

ROW_NUMBER()函数对于相同成绩，会生成连续排序，两人都是10分，一个是第二名，一个是第三名。

RANK()函数对于相同成绩，排名会重复，并且丢弃一个排名，两个人都是10分，两个都是第二名，没有第三名，直接到第四名。

DENSE_RANK()函数对于相同成绩，排名会重复，不会丢失排名，两人都是10分，两个都是第二名，下一名是第三名。