eg体育馆的人流量
表:
Stadium
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | visit_date | date | | people | int | +---------------+---------+ visit_date 是该表中具有唯一值的列。 每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people) 每天只有一行记录,日期随着 id 的增加而增加编写解决方案找出每行的人数大于或等于
100
且id
连续的三行或更多行记录。返回按
visit_date
升序排列 的结果表。
表 t1,t2 和 t3 相同,需要考虑添加哪些条件能够得到想要的结果。以 t1 为例,它有可能是高峰期的第 1 天,第 2 天,或第 3 天。
t1 是高峰期第 1 天:(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
t1 是高峰期第 2 天:(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
t1 是高峰期第 3 天:(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
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)
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)
)
order by t1.id