LeetCode数据库题180. 连续出现的数字

题目链接

知识点：

• CASE （WHEN+ 条件语句 + THEN + 结果）（可重复多条）END + AS + 结果名
• MySQL变量的定义和赋值使用

解决方法：

• 方法一（效率更高，且可以适用ID不连续的情况）

SELECT DISTINCT NUM AS ConsecutiveNums
FROM (
    SELECT NUM,
        CASE
            WHEN @NUM = NUM THEN @COUNT := @COUNT + 1
            WHEN (@NUM := NUM) IS NOT NULL THEN @COUNT := 1
            ELSE @COUNT := 1
        END AS CNT 
    FROM Logs, (SELECT @NUM := NULL, @COUNT := NULL) AS T
) AS TEMP
WHERE TEMP.CNT >= 3;

• 方法二 (只使用于ID连续的情况）

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
;