表:Stadium
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | visit_date | date | | people | int | +---------------+---------+ visit_date 是该表中具有唯一值的列。 每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people) 每天只有一行记录,日期随着 id 的增加而增加
编写解决方案找出每行的人数大于或等于 100
且 id
连续的三行或更多行记录。
返回按 visit_date
升序排列 的结果表。
查询结果格式如下所示。
示例 1:
输入:
Stadium
表:
+------+------------+-----------+
| id | visit_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-09 | 188 |
+------+------------+-----------+
输出:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
实现代码:
with t1 as (
select id, visit_date, people,
id - row_number() over (order by id) as rk
from stadium
where people >= 100
)
select id, visit_date, people
from t1
where rk in(
select rk
from t1
group by rk
having count(rk) >= 3
)
注意点:
1. with t1 as ( select语句 )
可以创建表t1,用于from
优点:减少阅读困难
2. row_number()
row_number() over (partition by ... order by ...)
依大小返回1,2,3,4,……一串数字
3. 列1 in 列2
遍历列1数据,选出所有在列2里的数据
4. 对单列使用 group by
rk rk
1 1
1 group by rk 2
2 ------------------>
2
2
2
---------------------------------------------------------
rk rk
1 2
1 group by rk
2 having count(rk>=3)
2 ------------------>
2
2