知识点:
- 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
;