Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
For example, given the above Logs
table, 1
is the only number that appears consecutively for at least three times.
题目大意:写一个SQL,查出表Logs中连续出现至少3次的数字。
最简单的方法就是直接使用一套SELECT-FROM-WHERE语句
# Write your MySQL query statement below
SELECT DISTINCT L1.Num ConsecutiveNums
FROM Logs L1
JOIN Logs L2 ON L1.Id + 1 = L2.Id
JOIN Logs L3 ON L1.Id + 2 = L3.Id
WHERE L1.Num = L2.Num AND L1.Num = L3.Num
ORDER BY L1.Num
上面两种方法可以用于找到至少三次连续出现的数字,如果将连续出现的数字扩展到N个,按照上面思路写出的SQL语句就会比较长。因此可以用下面这种方式来查询:
SELECT DISTINCT Num
FROM (
SELECT Num,
CASE
WHEN @prev = Num THEN @count := @count + 1
WHEN (@prev := Num) IS NOT NULL THEN @count := 1
END CNT
FROM Logs, (SELECT @prev := NULL) X
ORDER BY Id
) AS A
WHERE A.CNT >= N