SQL180. 连续出现的数字

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 是唯一连续出现至少三次的数字。

解这道题之前一直没理解，为啥最后相减是7555

1、先给这些id重新编号，连续的号

SELECT Id,Num,
row_number() over(order by id) as SerialNum
FROM three;

2、根据num值重新分组，分别排序

SELECT Id,Num,
ROW_NUMBER() over(partition by Num order by Id) as SerialGroup
FROM three;

3、相减！！！将SerialNum-SerialGroup，一定要记住，是根据id值来进行相减的

SELECT Id,Num,
row_number() over(order by id) as SerialNum,
row_number() over(partition by Num order by Id) as SerialGroup,
row_number() over(order by id) - row_number() over(partition by Num order by Id) as SerialNumberSubGroup
FROM three;

所以你会发现，这一行是在这里

4、统计数量超过3的，就是连续3次的数字（要去重）

SELECT DISTINCT Num as ConsecutiveNums  FROM (
SELECT Num,COUNT(1) as SerialCount FROM
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM three ) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result;