MYSQL查询至少连续N次出现的相同数字

基础案例：

表：Logs

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

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

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

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

 

示例 1:

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 11  | 1   |
| 21  | 1   |
| 31  | 1   |
| 41  | 2   |
| 51  | 1   |
| 61  | 2   |
| 71  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

通用解法

场景一

如同案例中id, 是主键且自增
那么我们先用row_number排序一下

SELECT
	id,
	num,
	ROW_NUMBER() over(ORDER BY id) rn
from Logs

然后我们再用以num作为分组字段用rank() 进行排序

SELECT
	id,
	num,
	RANK() over(PARTITION by num ORDER BY id) rn
from Logs

如果两个rn相减会是什么情况

SELECT
	id,
	num,
	ROW_NUMBER() over(ORDER BY id)- RANK() over(PARTITION by num ORDER BY id) rn
from Logs

出现0，就是意味着是连续相同数字，几个0就是几次

根据num，rn分组聚合

with result as (
	SELECT
		id,
		num,
		ROW_NUMBER() over(ORDER BY id)- rank() over(PARTITION by num ORDER BY id) rn
	from Logs
)
SELECT num, rn, count(*) count FROM result
GROUP BY num, rn

根据count筛选N即

最终代码如下：

with result as (
	SELECT
		id,
		num,
		ROW_NUMBER() over(ORDER BY id)- rank() over(PARTITION by num ORDER BY id) rn
	from Logs
),
result2 as (
	SELECT num, rn, count(*) count FROM result
	GROUP BY num, rn
	HAVING count >= 3
)

SELECT
	distinct num ConsecutiveNums
from result2

场景二

如果案例中id, 不是主键且非递增

如下：

我们就不能用排序了，这样会破坏顺序

解决方案
第一步：利用变量，赋值自增

SELECT
	num,
	CASE 
	  WHEN @prev = num THEN @count := @count + 1  
	  WHEN (@prev := num) is not null then @count := 1 		 
  END as tt
from Logs, (select @prev := null,@count := null) t

逻辑如下：

1. 生成两个变量，select @prev := null,@count := null 放在临时表t里，@prev用作比较，如果当前值和上一个相同，@count自增+1
2. @prev初始化为null，所以一旦执行第一个case when (WHEN @prev = num THEN @count := @count + 1 ) 结果一定是false, 所以必然进入第二个判断语句
3. 进入第二判断语句（WHEN (@prev := num) is not null then @count := 1 ）首先赋值@prev=第一个num，然后@count被赋值1
4. 之后就开始正常的循环判断，如果当前值和上一个相同@count自增+1，否则走第二case when @prev被重新赋值当前num，然后@count=1。继续开启循环

结果如下图

然后判断下count >= N 即可

with result as (
  SELECT
		num,
		CASE 
			WHEN @prev = num THEN @count := @count + 1  
			WHEN (@prev := num) is not null then @count := 1 		 
		END as count
  from Logs, (select @prev := null,@count := null) t
)

SELECT DISTINCT num ConsecutiveNums from result
where count >= 3