题10:
根据下面的表编写一个SQL查询以找出每行的人数大于或等于100且id连续的三行或更多行记录,返回按visit-date升序排列的结果表。
(1)查询人流量超过 100 的记录,然后将结果与其自身的临时表连接(自连接)。
(2)连续记录分为三种情况顺序,以第一表为例:
- a.id = b.id-1 and b.id = c.id -1
- a.id = b.id-1 and a.id = c.id +1
- a.id = b.id+1 and b.id = c.id +1
代码如下:
select distinct a.*
from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
(a.id = b.id-1 and b.id = c.id -1) or
(a.id = b.id-1 and a.id = c.id +1) or
(a.id = b.id+1 and b.id = c.id +1)
)
order by visit_date asc;