2021.1.1 第一天，元旦快樂
LeetCode 從零單刷個人筆記整理（持續更新）
github：https://github.com/ChopinXBP/LeetCode-Babel
傳送門：體育館的人流量
X 市建了一個新的體育館，每日人流量資訊被記錄在這三列資訊中：序號 (id)、日期 (visit_date)、 人流量 (people)，
請撰寫一個查詢陳述句，找出人流量的高峰期，高峰期時，至少連續三行記錄中的人流量不少于100，
例如，表 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-08 | 188 |
+------+------------+-----------+
對于上面的示例資料，輸出為：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
提示：
每天只有一行記錄，日期隨著 id 的增加而增加，
SELECT DISTINCT s1.id, s1.visit_date, s1.people
FROM stadium s1, stadium s2, stadium s3
WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100 AND (
((s1.id = s2.id - 1) AND (s1.id = s3.id - 2))
OR ((s1.id = s2.id - 1) AND (s1.id = s3.id + 1))
OR ((s1.id = s2.id + 1) AND (s1.id = s3.id + 2))
)
ORDER BY s1.id;
SELECT id, visit_date, people
FROM (
SELECT r1.*, @flag := if((r1.countt >= 3 OR @flag = 1) AND r1.countt != 0, 1, 0) AS flag
FROM (
SELECT s.*, @count := if(s.people >= 100, @count + 1, 0) AS `countt`
FROM stadium s, (SELECT @count := 0) b
) r1, (SELECT @flag := 0) c
ORDER BY id DESC
) result
WHERE flag = 1 ORDER BY id;
#Coding一小時，Copying一秒鐘，留個言點個贊唄，謝謝你#
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/243646.html
標籤：其他