LeetCode | database | Human Traffic of Stadium

• 题目描述
  x城市建立了一个新的stadium,并有下面名为stadium的表。写出一个查询语言找出人数连续三天及三天以上大于100的顾客信息。
  stadium表
  输出结果

• 题目解答
  连续给表起三次别名，再利用where语句选择题目要求的条件即可。

select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
     (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) #t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)  #t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)  #t3, t2, t1
)
order by t1.id
;