背景:stadium表(id,date, people),输出人数大于等于100且连续记录数大于等于3的记录,其中id是随着date增加
思路:使用三表的自然连接,缺点是没有通用性
sql语句如下:
select distinct s1.* from stadium s1, stadium s2, stadium s3 where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
and ((s2.id - s1.id = 1 and s3.id - s2.id = 1) or (s1.id - s2.id = 1 and s2.id - s3.id = 1) or (s1.id - s2.id = 1 and s3.id - s1.id = 1)) order by s1.id;
另外一种方法是通过用户变量来实现,这种通用性比较好
select id, date, people
from (select id, date, people,
case when consecutive >= 3 then @pending := 3
else @pending := @pending - 1
end as tmp,
if(@pending > 0, 1, 0) as include
from (select id, date, people,
case when people >= 100 then @count := @count + 1
else @count := 0
end as consecutive
from stadium, (select @count := 0) init1
) tmp, (select @pending := 0) init2
order by 1 desc
) otmp where include = 1
order by 1;