180. 连续出现的数字
题目描述
编写一个 SQL
查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
此题解法较多,这里主要分享三种解法。
解法一
可以给连续出现的数字一个编号,然后再选择编号大于2
的数字,最后再给这些数字distinct
一下。重点在于如何给连续出现的数字一个编号。这里可以考虑定义两个变量@c_rank
和@p_value
。其中c_rank
存连续出现的数字的编号,初始化为0
;p_value
存前一行记录的数字,初始化为NULL
。在遍历Logs
表时,如果当前行的数字与前一行的数字相同,则连续出现,需将c_rank
+ 1
,否则p_value
赋值为当前行的数字,c_rank
赋值为1
。
参考SQL(MySQL v5.6)
如下:
set @p_value := NULL, @c_rank := 0;
select
L1.Id, L1.Num,
case
when @p_value = L1.Num then @c_rank := @c_rank + 1
when @p_value := L1.Num then @c_rank := 1
end as `Rank`
from Logs L1
执行结果如下
Id | Num | Rank |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 1 | 3 |
4 | 2 | 1 |
5 | 1 | 1 |
6 | 2 | 1 |
7 | 2 | 2 |
最后再选择Rank
值大于2
的数字distinct
一下。该解法的好处是,不需要Id
值连续。
解法二
由于题目中Id
值连续连续,可以在遍历每一行记录时检查前面两行记录的数字是不是相等。最后再distinct
一下。
参考SQL(MySQL v5.6)
如下:
select distinct(L4.Num) as ConsecutiveNums
from(
select L1.Num
from `Logs` L1
where L1.Num = (select L2.Num from `Logs` L2 where L2.Id = L1.Id - 1)
and L1.Num = (select L3.Num from `Logs` L3 where L3.Id = L1.Id - 2)
) as L4
解法三
使用连接,其实这种方法和方法二有很大相似之处(相关子查询,内部一般转化会为连接)。
参考SQL(MySQL v5.6)
如下:
SELECT DISTINCT
L1.Num AS ConsecutiveNums
FROM
Logs L1,
Logs L2,
Logs L3
WHERE
L1.Id = L2.Id - 1
AND L2.Id = L3.Id - 1
AND L1.Num = L2.Num
AND L2.Num = L3.Num