Human Traffic of Stadium

/ mysql / 没有评论 / 433浏览

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

Note:
Each day only have one row record, and the dates are increasing with id increasing.

大概意思是查出人数大于等于100人,至少连续三天的数据。

我的sql语句:

select e.id,e.date,e.people from stadium e where e.id in(
select b.id from
(select a.id,a.id+1 as id1,a.id+2 as id2,a.date,a.people from stadium a where a.people>=100) b,stadium c,stadium d
where b.id1=c.id and b.id2=d.id and c.people>=100 and d.people>=100
)
or e.id in
(
select b.id1 from
(select a.id,a.id+1 as id1,a.id+2 as id2,a.date,a.people from stadium a where a.people>=100) b,stadium c,stadium d
where b.id1=c.id and b.id2=d.id and c.people>=100 and d.people>=100

)
or e.id in
(
select b.id2 from
(select a.id,a.id+1 as id1,a.id+2 as id2,a.date,a.people from stadium a where a.people>=100) b,stadium c,stadium d
where b.id1=c.id and b.id2=d.id and c.people>=100 and d.people>=100
)
order by e.id asc

如果可以建立临时表的话,可以把下面语句放在临时表

(
select b.id2 from
(select a.id,a.id+1 as id1,a.id+2 as id2,a.date,a.people from stadium a where a.people>=100) b,stadium c,stadium d
where b.id1=c.id and b.id2=d.id and c.people>=100 and d.people>=100
)
点击这里给我发消息
嘿!有什么能帮到您的吗?