Human Traffic of Stadium-K6K4.com

原题： https://leetcode.com/problems/human-traffic-of-stadium/description/

题意： X城市建立了一个新的体育场，每天有很多人访问，统计数据保存为这些列：id，date，people。请写一个查询，显示连续3行以上的人数超过100（含）的记录。

约定：（1）每天只有一行记录，日期随着id的增加而增加。

例子：

给定表格：
+------+------------+-----------+
| 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       |
+------+------------+-----------+
输出：
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

标签： people、stadium、traffic、human、date、面试

猜你感兴趣的圈子：

LeetCode交流圈

SLPH

2017-08-13 16:15:52 1楼#1层

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)  -- t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;

取消

回复

推荐圈子

LeetCode交流圈