601. 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.


大致题意:

求连续三行人数超过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) 
)
);




  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值