1、编写一个 SQL 查询,查找所有至少连续出现三次的数字。
答案1:
借此学习mysql定义变量的用法
SELECT
distinct num AS ConsecutiveNums
FROM
(
SELECT
r.num,
IF
( @num = r.num, @i := @i + 1, @i := 1 ) count,
@num := r.num tt
FROM
( SELECT * FROM `Logs` l,( SELECT @i := 0 ) t ) r
) z
WHERE
count > 2;
答案2:
二维数组也可以用in
SELECT DISTINCT
Num AS ConsecutiveNums
FROM
`Logs`
WHERE
( Id + 1, Num ) IN ( SELECT * FROM `Logs` )
AND ( Id + 2, Num ) IN (SELECT* FROM`Logs`);