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 tablestadium
:
+------+------------+-----------+
| 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的所有行(注意是连续三行并不是连续三天);
贴答案:
select s.id,s.date,s.people from stadium s where people >= 100 and
(
(
(select people from stadium where id = s.id + 1) >= 100
and
(select people from stadium where id = s.id - 1) >= 100
)
or
(
(select people from stadium where id = s.id - 1) >= 100
and
(select people from stadium where id = s.id - 2) >= 100
)
or
(
(select people from stadium where id = s.id + 1) >= 100
and
(select people from stadium where id = s.id + 2) >= 100
)
);
这道题难点在于条件筛选上,本来博主以为要用到循环语句,但是仔细审题后发现,条件可以用有限语句概括。
连续三行游客大于等于100,即要求本行大于100,上一行下一行都大于100,或者,下两行大于100,或者上两行大于100
思路清楚后,sql语句表述即可。也有另一种解法,与此大同小异
select * from stadium a where a.people >= 100 and
(
(
a.id+1 in (select id from stadium where people >= 100)
and
a.id+2 in (select id from stadium where people >= 100)
)
or
(
a.id-1 in (select id from stadium where people >= 100)
and
a.id+1 in (select id from stadium where people >= 100)
)
or
(
a.id-1 in (select id from stadium where people >= 100)
and
a.id-2 in (select id from stadium where people >= 100)
)
);