タイトル5:MySQL------Consecutive Number
770 ワード
Write a SQL query to find all numbers that appear at least three times consecutively.
For example, given the above
テーマ解答
+----+-----+
| 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. テーマ解答
# Write your MySQL query statement below
select distinct Num from (
select
Num,
case
when @prevNum = Num then @count := @count + 1
when (@prevNum := Num) is not null then @count := 1
end n
from Logs, (select @prevNum := NULL) r
order by Id
) a where n >= 3