【LC中等 】180. 连续出现的数字（五种方法实现）

❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈，孜孜不倦

1.题目描述

表：Logs

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

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

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

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

示例 1:

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字

2.代码实现

• 方法一：使用自连接

select distinct a.Num as ConsecutiveNums
from Logs as a join Logs as b join Logs as c
on a.Num=b.Num and b.Num=c.Num and a.id=b.id-1 and b.id=c.id-1;

因为join连接是有顺序的，先a与b进行join连接，然后连接的结果再与c进行join连接，最好不要直接三个连等于

select distinct a.Num as ConsecutiveNums
from Logs as a join Logs as b join Logs as c
on a.Num=b.Num=c.Num and a.id=b.id-1 and b.id=c.id-1;

• 方法二：巧妙使用多表查询

SELECT DISTINCT Num AS ConsecutiveNums 
FROM Logs 
WHERE (Id+1, Num) IN (SELECT * FROM Logs)
AND (Id+2, Num) IN (SELECT * FROM Logs)

• 方法三：使用窗口函数

  利用窗口函数求出diff后进行分组，再用having子句过滤

select distinct num as ConsecutiveNums
from (select num, id + 1 - row_number() over(partition by num order by id) as diff
      from Logs) t
group by num, diff
having count(*) >= 3

• 方法四：利用用户变量实现对连续出现的值进行计数：

select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

与自关联或自连接相比，这种方法的效率更高，不受Logs表中的Id是否连续的限制，而且可以任意设定某个值连续出现的次数。

1. 逻辑：构建两个变量@prev 和@count ，前者用于与Num做比较判断，后者用于@prev和Num相等时的条件计数；

2. (select @prev := null,@count := null) as t 这句的作用是初始化两个变量，并将初始化后的变量放到一张临时表t中，:=符号在MySQL中是赋值的意思；

3. when @prev = Num then @count := @count + 1和when (@prev := Num) is not null then @count := 1 这两个语句不能交换顺序，赋值语句永远非NULL，所以一旦执行顺序来到了第二个when，@count 是一定会被赋值为1的，后者放到前面的话就达不到计数的目的；

4. (@prev := Num) is not null这部分去掉后面加的判断，SQL也能正常执行，上面SQL中case when的这种用法，when后是判断条件，赋值后又加判断，我原以为这样会好理解点；

5. case when本质是一个函数，有值时就返回内部处理得到的值，无值就返回NULL，针对每一个Num，上面SQL中的case when 都会有一个计数，并把这个计数返回给CNT。

• 方法五：巧用lag lead函数

SELECT DISTINCT
	q.Num AS ConsecutiveNums 
FROM
	(
	SELECT
		Id,
		Num,
		LEAD( Num ) OVER ( ORDER BY Id ) AS next_sale_value,
		Lag( Num ) OVER ( ORDER BY Id ) AS last_sale_value 
	FROM
	LOGS 
	) q 
WHERE
	q.next_sale_value = q.Num 
	AND q.last_sale_value = q.Num