LeetCode 601. Human Traffic of Stadium

背景：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;