MySQL之LeetCode-180(连续出现的数字)

题目（中等）

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

±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+

解题语句

刚开始没有审清题，以为是至少出现三次，写下了下面的语句：

SELECT
  t.num AS ConsecutiveNums
FROM
  (SELECT
    l.num,
    SUM(
      CASE
        WHEN l.num = n.num
        THEN 1
      END) AS cou
  FROM
    LOGS AS l,
    (SELECT DISTINCT
      num
    FROM
      LOGS) AS n
  GROUP BY l.num) AS t
WHERE t.cou >= 3

解法一：连续出现三次就自连接三次

SELECT DISTINCT
    l.Num AS 'ConsecutiveNums'
  FROM
    LOGS l,
    LOGS l1,
    LOGS l2
  WHERE l.Id = l1.Id - 1
    AND l.Id = l2.Id - 2
    AND l.Num = l1.Num
    AND l.Num = l2.Num

这种解法需要id是连续的，而且不易扩展，如果要求变成连续出现N次，就需要自连接N次。

解法二：在查询语句中使用变量

SELECT DISTINCT
      Num AS ConsecutiveNums
    FROM
      (SELECT
        Num,
        @count := (@count * (@prev = (@prev := Num)) + 1) COUNT
      FROM
        LOGS,
        (SELECT
          @count := 0,
          @prev := - 1) b) a
    WHERE a.count > 2

如果不明白先执行：

SELECT
        Num,
        @count := (@count * (@prev = (@prev := Num)) + 1) COUNT
      FROM
        LOGS,
        (SELECT
          @count := 0,
          @prev := - 1) b

得到结果：

输入
{“headers”: {“Logs”: [“Id”, “Num”]}, “rows”: {“Logs”: [[1, 1], [2, 1], [3, 1], [4, 2], [5, 1], [6, 2], [7, 2]]}}
输出
{“headers”:[“Num”,“COUNT”],“values”:[[1,1.0],[1,2.0],[1,3.0],[2,1.0],[1,1.0],[2,1.0],[2,2.0]]}
预期结果
{“headers”:[“ConsecutiveNums”],“values”:[[1]]}
看到这个结果应该就明白这个解法的思路了。