LeetCode数据库180.连续出现的数字

简单记录一下刷LeetCode数据库的过程

注：

这道题官方题解给的是连结三个表，但是这种方法可复用性不强。于是学习了一下评论里一个大佬给出的方法，写了点自己的理解。
↓

题目：180.连续出现的数字

题目链接：https://leetcode-cn.com/problems/consecutive-numbers/
表：Logs

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| num | varchar |
±------------±--------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示：
Logs 表：
±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+

Result 表：
±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+
1 是唯一连续出现至少三次的数字。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路

select *,row_number() over (order by id) as row_id, 
row_number() over (partition by Num order by id) as row_num
from `Logs`;

row_id是按id排序的行号
row_num是按num分组，id排序的行号

发现若id连续且num相等（num连续出现）则sub = row_num - row_id是一个定值

select *,row_number() over (order by id) - row_number() over (partition by Num order by id) as sub
from `Logs`;

统计每个Num连续出现的次数：即Num和sub都相等的行数。可以按Num和sub分组，再用count(*)

select Num, count(1)
		from (select *,row_number() over (order by id) - row_number() over (partition by Num order by id) as sub
				from `Logs`) as b
		group by Num, sub
        having count(1) >= 3;

最后按题目要求的输出

select distinct Num as ConsecutiveNums
from (select Num, count(1)
		from (select *,row_number() over (order by id) - row_number() over (partition by Num order by id) as sub
				from `Logs`) as b
		group by Num, sub
        having count(1) >= 3
) as result;

知识点

ROW_NUMBER() 从1开始，为每一条记录返回一个数字