---
tags: LeetCode, coding
---

# Leet code sql 180. Consecutive Numbers
## description

# sql scheme
Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')

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.

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

## solutions
> 同排名一樣使用變數(sql 178. Rank Scores)，每讀一列取Num欄位值與前一列比較，相同計數+1，不同就從1重算

SELECT distinct Num as ConsecutiveNums
FROM (
    SELECT 
        Num,
        IF(@prev = Num, @count := @count + 1, @count := 1) as count,
        @prev := Num
    FROM 
        Logs,
        (SELECT @count := 1, @prev := null) v
) temp
WHERE count >= 3;

## result