題目:
表:Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主鍵
每日人流量資訊被記錄在這三列資訊中:序号 (id)、日期 (visit_date)、 人流量 (people)
每天隻有一行記錄,日期随着 id 的增加而增加
編寫一個 SQL 查詢以找出每行的人數大于或等于 100 且 id 連續的三行或更多行記錄。傳回按 visit_date 升序排列的結果表。
查詢結果格式如下所示。
Stadium table:
+------+------------+-----------+
| 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 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 為 5、6、7、8 的四行 id 連續,并且每行都有 >= 100 的人數記錄。
請注意,即使第 7 行和第 8 行的 visit_date 不是連續的,輸出也應當包含第 8 行,因為我們隻需要考慮 id 連續的記錄。
不輸出 id 為 2 和 3 的行,因為至少需要三條 id 連續的記錄。
解題思路:
這道題需要提前用With臨時空間,是因為where子句中需要再次調用from中選取的表
這裡再聊一下sql的運作順序:from -> where -> group by -> select -> order by -> limit
即臨時表t1 需要再from 和 where 中都用到是以需要提前定義
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsICM38FdsYkRGZkRG9lcvx2bjxiNx8VZ6l2cs0TPB1ENJRUTwcmeNBDOsJGcohVYsR2MMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnL2QzNzAzM1EjM4EzNwEjMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
代碼:
with t1 as (
select id,visit_date,people,id-rank() over(order by id) 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) #對我們得出的唯一的rk進行group by 和having 篩選(注意having是SQL中唯一的剪裁表的action)
其中,rank()函數為排名函數,預設為升序,over()一般和rank()連用。