Leetcode: sql 180. 连续出现的数字 掌握在数据库中的循环语句

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

+----+-----+
| Id | Num |
+----+-----+
| 0  |  1  |
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  1  |
| 7  |  3  |
| 8  |  3  |
| 9  |  3  |
+----+-----+

例如，给定上面的 Logs 表， 1 和 3 是连续出现至少三次的数字。

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

• 正解 就TM要在SQL里写循环(因为实际情况下ID不一定能自增)
  思路： 一个变量@pre统计上一个数字，一个变量@cnt统计该数字连续出现了几次。

SELECT DISTINCT a.Num 
FROM  
(
SELECT t.`Num`,
       @cnt:= IF(@pre=t.Num,@cnt+1, 1) cnt,
       @pre:= t.Num pre
FROM LOGS t, 
	(SELECT @pre:= NULL, @cnt:= 0) b
) a
WHERE  a.cnt>=3

• 先看看怎么创建变量

SELECT @pre:= NULL, @cnt:= 0

因为是选择变量, 所以也不要SELECT FROM, 直接SELECT就行

• 与之前的logs表做笛卡尔积

SELECT *
FROM LOGS t,
     (SELECT @pre:= NULL, @cnt:= 0) b

注意 Every derived table must have its own alias (派生的表必须要有别名b)

• 笛卡尔积之后的表已经和我们理想的形态差不多了， 变量变量神奇的地方就在于他可以变化！表中的变量列是可以人为改变的！！

SELECT t.Num, t.id,
       @cnt:= IF(@pre=t.Num, @cnt+1, 1) cnt, -- 如果pre(记录上一行的数字)等于这一行的数字，那么cnt+1, 否则重新开始=1
       @pre:= t.Num pre                      -- pre(记录本行数字，下一行的cnt比较时使用)
FROM LOGS t,
     (SELECT @pre:= NULL, @cnt:= 0) b

• 结束

SELECT DISTINCT a.Num ConsecutiveNums
FROM(
SELECT t.Num, t.id,
       @cnt:= IF(@pre=t.Num, @cnt+1, 1) cnt, -- 如果pre(记录上一行的数字)等于这一行的数字，那么cnt+1, 否则重新开始=1
       @pre:= t.Num pre                      -- pre(记录本行数字，下一行的cnt比较时使用)
FROM LOGS t,
     (SELECT @pre:= NULL, @cnt:= 0) b
     ) a
WHERE a.cnt >= 3 

• 追加

SET @pre:=NULL; -- 变量的赋值要用;结尾
SET @cnt:=0;

SELECT t.Num ,
       @cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
       @pre:=t.Num pre
  FROM LOGS t

该语句同样可以得到笛卡尔积的结果，不过之后的语句执行失败FROM后面就要直接跟一个DQL，返回一个表。

SELECT DISTINCT a.Num ConsecutiveNums
FROM 
(SET @pre:=NULL;
 SET @cnt:=0;

SELECT t.Num ,
       @cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
       @pre:=t.Num pre
  FROM LOGS t) a 
WHERE a.cnt >=3

调整顺序后可以在本地通过，leetcode不行，就用笛卡尔积的思想去解决！！！简单易懂。

SET @pre:=NULL;
SET @cnt:=0;
SELECT DISTINCT a.Num ConsecutiveNums
FROM 
(

SELECT t.Num ,
       @cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
       @pre:=t.Num pre
  FROM LOGS t) a 
WHERE a.cnt >=3