一、题目描述
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+
二、题解
方法一:
# Write your MySQL query statement below
SELECT
distinct b.Num as ConsecutiveNums
FROM
Logs a,Logs b,Logs c
where a.Id = b.Id - 1 and b.Id = c.Id - 1 and a.Num = b.Num and b.Num = c.Num
方法二:使用窗口函数
# Write your MySQL query statement below
SELECT
distinct t.Num as ConsecutiveNums
FROM
(
SELECT
Num,
LAG(Num,1) over(order by Id) Num1,
LAG(Num,2) over(order by Id) Num2
FROM
Logs
)t
where t.Num = t.Num1 and t.Num1 = t.Num2