【leetcode database】Human Traffic of Stadium

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.

这题的难度属于hard级别，但我觉得最多也就是一般难度。初看起来这题好像有点不知所措，但仔细分析却能发现其实条件很简单,只要满足以下任意三个条件即可：

1.id in (x,x+1,x+2) 的记录的people >= 100;

2.1.id in (x,x+1,x-1) 的记录的people >= 100;

3.1.id in (x,x-1,x-2) 的记录的people >= 100;

代码如下:

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) 
)
);

 

转载于:https://www.cnblogs.com/seyjs/p/7081517.html