# 180.(Medium)连续出现的数字

表：Logs

```
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。
```

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

```
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 是唯一连续出现至少三次的数字。
```

来源：力扣（LeetCode）

链接：<https://leetcode-cn.com/problems/consecutive-numbers>

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

## Solution

* select 里的每个字段的相关计算是按顺序执行的
* case when 的条件判断句是按顺序执行的，既能判断又能赋值。。。

```sql
SELECT DISTINCT Num ConsecutiveNums
FROM
(
SELECT Num,
    case 
    WHEN @prev = Num then @cnt:=@cnt+1              -- when clause 1
    WHEN (@prev:= Num) is not null then @cnt:=1     -- when clause 2
    end cnt
FROM Logs, (SELECT @prev:=null, @cnt:=null) t1
) t2
WHERE cnt >=3;
```

* when clause 1 在第一行必跳过
* when 2 条件总是true，执不执行完全取决于when 1 是否为true：第一条false了，就执行 @cnt:=1；第一条 true了，压根到不了这一条。

## Table Schema

```sql
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');
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://zqt0.gitbook.io/leetcode/sql/180.consecutive-numbers.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.