数据库刷题记录

一、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;