问题

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

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

准备

create database leecote180;
use leecote180;
create table logs(
id varchar(20),
num float(2));


insert into logs values
(1,1),
(2,1),
(3,1),
(4,2),
(5,1),
(6,2),
(7,2),
(8,3),
(9,3),
(10,3),
(11,3);

解答

#方法一：(有缺陷：只能显示出1个连续数字，具体请与方法二对比）
select l1.num as consecutivenums from logs l1,logs l2,logs l3
where l2.id=l1.id+1 and l3.id = l1.id +2 and l1.num = l2.num =l3.num;

#方法二：
select distinct l1.num as ConsecutiveNums from logs l1,logs l2,logs l3
where l2.id=l1.id+1 and l3.id = l1.id +2 and l1.num = l2.num and l2.num =l3.num;

#方法三：
select distinct num as ConsecutiveNums  from (
select num,
if(@num1=num,@num:=@num+1,@num:=1) as ConsecutiveNums1,
@num1:=num,
if(@num>=3,1,0) as rank1
from logs t1,
(select @num:=0,@num1:=null) t2) t3 where rank1=1;