问题

表：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 是唯一连续出现至少三次的数字

思路一

连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字

  1. SELECT *
  2. FROM
  3.     Logs l1,
  4.     Logs l2,
  5.     Logs l3
  6. WHERE
  7.     l1.Id = l2.Id - 1
  8.     AND l2.Id = l3.Id - 1
  9.     AND l1.Num = l2.Num
  10.     AND l2.Num = l3.Num;

image.png
注意：前两列来自 l1 ，接下来两列来自 l2 ，最后两列来自 l3

然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num;

思路二

要获取至少连续三次出现的数字，不能强力破解，如果是至少连续出现四次？(一百次？)，连接四个？(连接一百个?)，这种方法指定是不可取的

找解题答案，连续的相同Num的数字，但是Id有可能不是连续的，我们就需要通过对结果集再次编号，让其变成连续的
image.png
对原始数据编号，从1开始使用row_number() over(表达式)函数，使用Id来排序

SELECT Id,Num,
row_number() over(order by id) as SerialNum
FROM ContinueNumber

运行结果为:
image.png
使用原始数据另一维度排序，这些num值一样的分组排序，然后对其编号同样使用row_number() over(表达式)，参数：（num分组，id排序）row_number() over(partition by num order by id)

SELECT Id,Num,
row_number() over(partition by Num order by Id) as SerialGroup
FROM ContinueNumber

运行结果为:
image.png
通过上述1和上述2看一下有什么规律吗？两个列（SerialNum，SerialGroup）对应相减，只要连续，相减得到的值是一样的。不连续相减得到的值也不同

SELECT Id,Num,
      row_number() over(order by id) -
      row_number() over(partition by Num order by Id) as SerialNumberSubGroup
      FROM ContinueNumber

运行结果为:
image.png
通过上述3，通过列Num和列SerialNumberSubGroup分组，最后拿到Num，就是求得的数据,去重（distinct）指：有可能同一个数字在多处出现三次以上

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM 
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

运行结果为:
image.png