sql排序函数 rank() / dense_rank()

Rank排名函数

1、rank()

按照某字段的排序结果添加排名，但是他是跳跃的、间断的排名

partition by子句按照对应字段将结果集分为多个分区，然后order by子句按分数对结果集进行排序

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

例：按照score进行排名

SELECT score, rank() over(ORDER BY score desc) as 'Rank' 
FROM rank;
-->
+------+---------+
| score|   Rank  |
+------+---------+
|  100 |       1 |
|  100 |       1 |
|  95  |       3 |
|  95  |       3 |
|  95  |       3 |
|  90  |       6 |
|  89  |       7 |
+------+---------+

按照name进行分区，根据分数进行排名

select name, score ,rank() over(partition by name, order by score desc) 'Rank'
from rankl;
​
+------+------+---------+
| name | score|   Rank  |
+------+------+---------+
|  a   |  100 |       1 |
|  a   |  90  |       2 |
|  a   |  89  |       3 |
|  b   |  100 |       1 |
|  c   |  95  |       1 |
|  d   |  95  |       1 |
|  e   |  95  |       1 |
+------+------+---------+

2、dense_rank()

dense_rank()是的排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的，例如，1,2,2,3。

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

例：leetcode178. 分数排名

select score, dense_rank() over(order by score desc) 'rankd'
from Scores
​
输入: 
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
输出: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

例：leetcode185. 部门工资前三高的所有员工

从子表r中查询，子表包括需要查出来的字段，以及薪水rank排位，按部门id分组查询出每组(每部门)薪水从低到高的排名，然后外层查询的时候，只筛选rank排位在前三的人就行

select Department,Employee,Salary
from (select d.name Department, e.name Employee, e.salary Salary, dense_rank() over(partition by d.id order by e.salary desc)  'rank'
      from Employee e, Department d
      where e.departmentId = d.id
) r
where r.rank <= 3 ;