一、rank() over
:、dense_rank() over
、row_number() over
1.rank() over
:排名相同的两名是并列,但是占两个名次,1 1 3 4 4 6这种
2.dense_rank() over
:排名相同的两名是并列,共占一个名词,1 1 2 3 3 4这种
3.row_number() over
这个函数不需要考虑是否并列,哪怕根据条件查询出来的数值相同也会进行连续排名 1 2 3 4 5
例子:Leetcode 178. 分数排名
表: Scores
Column Name | Type |
---|---|
id | int |
score | decimal |
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。
编写 SQL 查询对分数进行排序。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
- 按 score 降序返回结果表。
查询结果格式如下所示。
示例 1:
输入:
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 |
常规写法:
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score ) as `Rank`
from Scores a order by a.score desc;
dense_rank() over
写法:
select
score,
(dense_rank() over (order by Score desc)) AS "rank"
from
Scores
二、 Leetcode 180. 连续出现的数字
表:Logs
Column Name | Type |
---|---|
id | int |
num | varchar |
- id 是这个表的主键。
- 编写一个 SQL 查询,查找所有至少连续出现三次的数字。
- 返回的结果表中的数据可以按 任意顺序 排列。
- 查询结果格式如下面的例子所示:
示例 1:
输入:
Logs 表:
Id | Num |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 1 |
6 | 2 |
7 | 2 |
输出:
Result 表:
ConsecutiveNums |
---|
1 |
解释:1 是唯一连续出现至少三次的数字。
解法:
select distinct L1.Num AS ConsecutiveNums
from
Logs L1,
Logs L2,
Logs L3
where
L1.Num = L2.Num
and
L2.Num = L3.Num
and
L1.Id = L2.Id + 1
and
L2.Id = L3.Id + 1;