原文連結：https://blog.csdn.net/weixin_48816093/article/details/127583702?spm=1001.2101.3001.6650.3&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-3-127583702-blog-107669318.235%5Ev43%5Epc_blog_bottom_relevance_base8&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-3-127583702-blog-107669318.235%5Ev43%5Epc_blog_bottom_relevance_base8&utm_relevant_index=6
一、專案員工 III
1.題目描述
專案表 Project：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) 是這個表的主鍵
employee_id 是員工表 Employee 的外來鍵
員工表 Employee：
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id 是這個表的主鍵
寫 一個 SQL 查詢語句，報告在每一個專案中經驗最豐富的僱員是誰。如果出現經驗年數相同的情況，請報告所有具有最大經驗年數的員工。
示例：
Project 表：
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表：
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
Result 表：
+-------------+---------------+
| project_id | employee_id |
+-------------+---------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
+-------------+---------------+
employee_id 為 1 和 3 的員工在 project_id 為 1 的專案中擁有最豐富的經驗。在 project_id 為 2 的專案中，employee_id 為 1 的員工擁有最豐富的經驗。
2.解題思路
解題思路：1）先連線表再排序
2）使用視窗函式排序以project_id為PARTITION分組以years為ORDER 降序排序
3）找出排序為1的即為結果
3.程式碼實現
#還是視窗函式經典題
SELECT project_id, employee_id
FROM(
SELECT
p.project_id,
p.employee_id,
RANK() OVER(PARTITION BY p.project_id ORDER BY e.experience_years DESC) AS r
FROM Project p
LEFT JOIN Employee e
ON p.employee_id = e.employee_id
) AS T
WHERE r = 1
二、1308. 不同性別每日分數總計
1.題目描述
表: Scores
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| player_name | varchar |
| gender | varchar |
| day | date |
| score_points | int |
+---------------+---------+
(gender, day)是該表的主鍵
一場比賽是在女隊和男隊之間舉行的
該表的每一行表示一個名叫 (player_name) 性別為 (gender) 的參賽者在某一天獲得了 (score_points) 的分數
如果參賽者是女性，那麼 gender 列為 'F'，如果參賽者是男性，那麼 gender 列為 'M'
寫一條SQL語句查詢每種性別在每一天的總分。
返回按 gender 和 day 對查詢結果 升序排序 的結果。
查詢結果格式的示例如下。
示例 1：
輸入：
Scores表:
+-------------+--------+------------+--------------+
| player_name | gender | day | score_points |
+-------------+--------+------------+--------------+
| Aron | F | 2020-01-01 | 17 |
| Alice | F | 2020-01-07 | 23 |
| Bajrang | M | 2020-01-07 | 7 |
| Khali | M | 2019-12-25 | 11 |
| Slaman | M | 2019-12-30 | 13 |
| Joe | M | 2019-12-31 | 3 |
| Jose | M | 2019-12-18 | 2 |
| Priya | F | 2019-12-31 | 23 |
| Priyanka | F | 2019-12-30 | 17 |
+-------------+--------+------------+--------------+
輸出：
+--------+------------+-------+
| gender | day | total |
+--------+------------+-------+
| F | 2019-12-30 | 17 |
| F | 2019-12-31 | 40 |
| F | 2020-01-01 | 57 |
| F | 2020-01-07 | 80 |
| M | 2019-12-18 | 2 |
| M | 2019-12-25 | 13 |
| M | 2019-12-30 | 26 |
| M | 2019-12-31 | 29 |
| M | 2020-01-07 | 36 |
+--------+------------+-------+
解釋：
女性隊伍:
第一天是 2019-12-30，Priyanka 獲得 17 分，隊伍的總分是 17 分
第二天是 2019-12-31, Priya 獲得 23 分，隊伍的總分是 40 分
第三天是 2020-01-01, Aron 獲得 17 分，隊伍的總分是 57 分
第四天是 2020-01-07, Alice 獲得 23 分，隊伍的總分是 80 分
男性隊伍：
第一天是 2019-12-18, Jose 獲得 2 分，隊伍的總分是 2 分
第二天是 2019-12-25, Khali 獲得 11 分，隊伍的總分是 13 分
第三天是 2019-12-30, Slaman 獲得 13 分，隊伍的總分是 26 分
第四天是 2019-12-31, Joe 獲得 3 分，隊伍的總分是 29 分
第五天是 2020-01-07, Bajrang 獲得 7 分，隊伍的總分是 36 分
2.解題思路
同樣是使用視窗函式的經典題目，這裡不是用排序的視窗函式而是使用聚合函式 + 視窗函式
SUM() OVER(PARTITION BY 性別 ORDER BY 日期) 因此思路如下：
1）以性別為分組，日期為排序構造視窗函式
2）直接搜尋得出結果
3.程式碼實現
# Write your MySQL query statement below
#直接用視窗函式 OVER
SELECT gender, day, SUM(score_points) OVER(PARTITION BY gender ORDER BY day) AS total FROM Scores
三、178. 分數排名
1.題目描述
表: Scores
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
Id是該表的主鍵。
該表的每一行都包含了一場比賽的分數。Score是一個有兩位小數點的浮點值。
編寫 SQL 查詢對分數進行排序。排名按以下規則計算:
分數應按從高到低排列。
如果兩個分數相等，那麼兩個分數的排名應該相同。
在排名相同的分數後，排名數應該是下一個連續的整數。換句話說，排名之間不應該有空缺的數字。
示例 1:
輸入:
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
輸出:
+-------+------+
| score | rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
2.解題思路
同樣是典型排序視窗函式使用 有重複排序如：1，2，2，3....使用
DENSE_RANK() OVER(PARTITION BY ORDER BY DESC) AS
因此直接一步視窗函式即可得出結果。
3.程式碼實現
SELECT score,
DENSE_RANK() OVER(ORDER BY score DESC) AS 'rank'
FROM Scores
四、184. 部門工資最高的員工
1.題目描述
表： Employee
+--------------+---------+
| 列名 | 型別 |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id是此表的主鍵列。
departmentId是Department表中ID的外來鍵。
此表的每一行都表示員工的ID、姓名和工資。它還包含他們所在部門的ID。
表： Department
+-------------+---------+
| 列名 | 型別 |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id是此表的主鍵列。
此表的每一行都表示一個部門的ID及其名稱。
編寫SQL查詢以查詢每個部門中薪資最高的員工。
按 任意順序 返回結果表。
查詢結果格式如下例所示。
示例 1:
輸入：
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
輸出：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
解釋：Max 和 Jim 在 IT 部門的工資都是最高的，Henry 在銷售部的工資最高。
2.解題思路
看見有重複的分組排序這裡單單使用GROUP BY 不能直接解決，可以使用視窗函式 或者GROUP BY 之後使用WHERE IN，這裡我們直接使用視窗函式 RANK() OVER() 解題思路如下：
1）首先確定視窗函式 以 departmentId分組 salary為排序，將Employee 表和Department 表連線來獲得分組排序好的臨時表
2）根據臨時表得出結果， 由於臨時表裡已經有了視窗函式獲得的排序結果，因此我們使用where篩選出排第一的記錄
3）這裡我們要注意使用視窗函式排序的升序降序。這裡由於所求的是最高工資，因此使用降序即DESC
3.程式碼實現
# Write your MySQL query statement below
#使用WHERE IN 的方法
#SELECT D.name AS Department, E.name AS Employee, E.salary AS Salary
#FROM Employee E
#LEFT join Department D
#ON E.departmentId = D.id
#WHERE (E.departmentId, Salary) IN (
# SELECT departmentId, MAX(salary)
# FROM Employee
# GROUP BY departmentId
#)
#直接用一下子視窗函式
SELECT Department,
Employee,
Salary
FROM(
SELECT d.name AS Department,
e.name AS Employee,
e.salary AS Salary,
RANK() OVER(PARTITION BY e.departmentId ORDER BY e.salary DESC) AS salary_rank
FROM Employee e
LEFT JOIN Department d
ON e.departmentId = d.id
) AS temp
WHERE salary_rank = 1
五、1321. 餐館營業額變化增長
1.題目描述
表: Customer
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| visited_on | date |
| amount | int |
+---------------+---------+
(customer_id, visited_on) 是該表的主鍵。
該表包含一家餐館的顧客交易資料。
visited_on 表示 (customer_id) 的顧客在 visited_on 那天訪問了餐館。
amount 是一個顧客某一天的消費總額。
你是餐館的老闆，現在你想分析一下可能的營業額變化增長（每天至少有一位顧客）。
寫一條 SQL 查詢計算以 7 天（某日期 + 該日期前的 6 天）為一個時間段的顧客消費平均值。average_amount 要 保留兩位小數。
查詢結果按 visited_on 排序。
查詢結果格式的例子如下。
示例 1:
輸入：
Customer 表:
+-------------+--------------+--------------+-------------+
| customer_id | name | visited_on | amount |
+-------------+--------------+--------------+-------------+
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
+-------------+--------------+--------------+-------------+
輸出：
+--------------+--------------+----------------+
| visited_on | amount | average_amount |
+--------------+--------------+----------------+
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
+--------------+--------------+----------------+
解釋：
第一個七天消費平均值從 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二個七天消費平均值從 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三個七天消費平均值從 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四個七天消費平均值從 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86
2.解題思路
本題有兩個函式需要留心：
1）視窗函式加上視窗滑動的資料範圍，首先是下面簡單的視窗函式：
[你要的操作] OVER ( PARTITION BY <用於分組的列名>
ORDER BY <按序疊加的列名>
ROWS <視窗滑動的資料範圍> )
接下來如果加上滑動視窗的範圍：
當前行 - current row
之前的行 - preceding
之後的行 - following
無界限 - unbounded
表示從前面的起點 - unbounded preceding
表示到後面的終點 - unbounded following
舉例子：
取當前行和前五行：ROWS between 5 preceding and current row --共6行
取當前行和後五行：ROWS between current row and 5 following --共6行
取前五行和後五行：ROWS between 5 preceding and 5 folowing --共11行
2）要取前七天的話至少得從第七天開始，那麼這裡運用 WHERE DATEDIFF(A, B)來進行篩選，即返回date1 - date2如下：
-- 1
SELECT DATEDIFF('2022-04-30','2022-04-29');
-- 0
SELECT DATEDIFF('2022-04-30','2022-04-30');
-- -1
SELECT DATEDIFF('2022-04-29','2022-04-30');
-- 0
SELECT DATEDIFF('2022-04-30','2022-04-30 14:00:00');
-- 1
SELECT DATEDIFF('2022-04-30 13:00:00','2022-04-29 14:00:00');
-- 10
SELECT DATEDIFF('2017-06-25 09:34:21', '2017-06-15');
接下來是我們的整體思路：
1）使用GROUP BY以日期為分組求和建立臨時表
2）根據上一步的臨時表，使用滑動視窗來求前七天平均值建立臨時表，當然這裡當前面沒有六天的日期並沒有篩選出去
3）根據上一次臨時表，使用WHERE DATEDIFF 篩選出前面有六天的選項做為結果
3.程式碼實現
# Write your MySQL query statement below
SELECT visited_on, amount, ROUND(amount / 7, 2) AS average_amount
FROM(
SELECT visited_on, SUM(amount) OVER(ORDER BY visited_on ROWS 6 PRECEDING) AS amount
FROM(
SELECT visited_on, SUM(amount) AS amount
FROM Customer
GROUP BY visited_on
) AS T
) AS TT
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6
六、1549. 每件商品的最新訂單
表: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
+---------------+---------+
customer_id 是該表主鍵.
該表包含消費者的資訊.
表: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| customer_id | int |
| product_id | int |
+---------------+---------+
order_id 是該表主鍵.
該表包含消費者customer_id產生的訂單.
不會有商品被相同的使用者在一天內下單超過一次.
表: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
| price | int |
+---------------+---------+
product_id 是該表主鍵.
該表包含所有商品的資訊.
寫一個SQL 語句, 找到每件商品的最新訂單(可能有多個).
返回的結果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果還有排序相同, 再以 order_id 升序排列.
查詢結果格式如下例所示。
示例 1：
輸入:
Customers表：
+-------------+-----------+
| customer_id | name |
+-------------+-----------+
| 1 | Winston |
| 2 | Jonathan |
| 3 | Annabelle |
| 4 | Marwan |
| 5 | Khaled |
+-------------+-----------+
Orders表：
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | product_id |
+----------+------------+-------------+------------+
| 1 | 2020-07-31 | 1 | 1 |
| 2 | 2020-07-30 | 2 | 2 |
| 3 | 2020-08-29 | 3 | 3 |
| 4 | 2020-07-29 | 4 | 1 |
| 5 | 2020-06-10 | 1 | 2 |
| 6 | 2020-08-01 | 2 | 1 |
| 7 | 2020-08-01 | 3 | 1 |
| 8 | 2020-08-03 | 1 | 2 |
| 9 | 2020-08-07 | 2 | 3 |
| 10 | 2020-07-15 | 1 | 2 |
+----------+------------+-------------+------------+
Products表：
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1 | keyboard | 120 |
| 2 | mouse | 80 |
| 3 | screen | 600 |
| 4 | hard disk | 450 |
+------------+--------------+-------+
輸出：
+--------------+------------+----------+------------+
| product_name | product_id | order_id | order_date |
+--------------+------------+----------+------------+
| keyboard | 1 | 6 | 2020-08-01 |
| keyboard | 1 | 7 | 2020-08-01 |
| mouse | 2 | 8 | 2020-08-03 |
| screen | 3 | 3 | 2020-08-29 |
+--------------+------------+----------+------------+
解釋：
keyboard 的最新訂單在2020-08-01, 在這天有兩次下單.
mouse 的最新訂單在2020-08-03, 在這天只有一次下單.
screen 的最新訂單在2020-08-29, 在這天只有一次下單.
hard disk 沒有被下單, 我們不把它包含在結果表中.
2.解題思路
此題依然可以用排序視窗函式 RANK() OVER(PARTITION BY o.product_id ORDER BY o.order_date DESC) 來解決，當能用排序視窗時，同時也能想到用GROUP BY 分組求出最大值建立臨時表， 然後用 WHERE (p.product_id, o.order_date) IN 臨時表 來篩選，這裡我們主要給出使用視窗函式解法，其中使用WHERE IN 也在註釋中給出。
視窗函式解題思路如下：
1）將ORDER表做排序，以o.product_id為分組，o.order_date降序分組做為臨時表
2）Products表左連線ORDER 表，將排序第一的篩選出來就是最新訂單啦，從而得出結果
3.程式碼實現
# Write your MySQL query statement below
#第一種 使用GROUP BY 但是效能較差
/**SELECT
p.product_name,
p.product_id,
o.order_id,
o.order_date
FROM Products p
LEFT JOIN Orders o
ON p.product_id = o.product_id
WHERE (p.product_id, o.order_date) IN (
SELECT product_id, MAX(order_date)
FROM Orders
GROUP BY product_id
)
ORDER BY p.product_name, p.product_id, order_id**/
SELECT
p.product_name,
p.product_id,
t.order_id,
t.order_date
FROM Products p
LEFT JOIN (
SELECT
o.product_id,
o.order_id,
o.order_date,
RANK() OVER(PARTITION BY o.product_id ORDER BY o.order_date DESC) AS t1
FROM Orders o
) AS t
ON p.product_id = t.product_id
WHERE t1 = 1
ORDER BY p.product_name, p.product_id, t.order_id
七、180. 連續出現的數字
1.題目描述
表：Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是這個表的主鍵。
編寫一個 SQL 查詢，查詢所有至少連續出現三次的數字。
返回的結果表中的資料可以按 任意順序 排列。
查詢結果格式如下面的例子所示：
示例 1:
輸入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
輸出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解釋：1 是唯一連續出現至少三次的數字。
2.解題思路
我覺得這個題解的核心思想是要想明白這個結論： 如果一個num連續出現時，那麼它出現的[真實序列]-它出現的次數一定是個定值。 因為：
1)假設一個num出現後，它的 真實序列 為 i ，同時假設它是第 k 次出現的； 差值為 i-k.
2)當它連續出現一次時，它的 真實序列 一定為 i+1 ; 它的出現次數顯然也會+1，為 k+1 ; 差值為 i+1-(k+1)=i-k.
3)當它連續出現第 n 次時，它的 真實序列 一定為 i+n；它出現的次數為 k+n;差值為 i+n-(k+n)=i-k.
4)如果它不連續出現，假設m個其他num出現之後，它又出現了，則它的真實序列為 i+n+m，而出現的次數為 k+n+1;差值為 i-k+m-1
5)之所以要ROW_NUMBER() OVER(ORDER BY Id)代替Id做為真實順序 是因為d 有起始是0的 導致相減是負數所以選ROW_NUMBER() OVER(ORDER BY Id)好一點 -，ROW_NUMBER() OVER(PARTITION BY Num ORDER BY Id)也是因為如此，在進行PARTITION BY Num後其真實順序會變
那麼我們分為4步：
1）對原始資料編號，從1開始使用 row_number() over(表示式) 函式，使用Id來排序既row_number() over(order by Id)
SELECT Id,Num,
row_number() over(order by id) as SerialNum
FROM ContinueNumber
結果如下：
2） 使用原始資料另一維度排序，這些num值一樣的分組排序，然後對其編號同樣使用row_number() over(表示式)，引數：（num分組，id排序）row_number() over(partition by num order by id)
SELECT Id,Num,
ROW_NUMBER() over(partition by Num order by Id) as SerialGroup
FROM ContinueNumber
結果：
3） 透過上述1和上述2 看一下有什麼規律嗎？兩個列（SerialNum，SerialGroup）對應相減，只要連續，相減得到的值是一樣的。不連續相減得到的值也不同。
SELECT Id,Num,
row_number() over(order by id) -
row_number() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber
結果為：
3.程式碼實現
# Write your MySQL query statement below
/**我覺得這個題解的核心思想是要想明白這個結論： 如果一個num連續出現時，那麼它出現的[真實序列]-它出現的次數一定是個定值。 因為：
1)假設一個num出現後，它的 真實序列 為 i ，同時假設它是第 k 次出現的； 差值為 i-k.
2)當它連續出現一次時，它的 真實序列 一定為 i+1 ; 它的出現次數顯然也會+1，為 k+1 ; 差值為 i+1-(k+1)=i-k.
3)當它連續出現第 n 次時，它的 真實序列 一定為 i+n；它出現的次數為 k+n;差值為 i+n-(k+n)=i-k.
4)如果它不連續出現，假設m個其他num出現之後，它又出現了，則它的真實序列為 i+n+m，而出現的次數為 k+n+1;差值為 i-k+m-1
5)之所以要ROW_NUMBER() OVER(ORDER BY Id)這裡Id 但Id 有起始是0的 導致相減是負數所以選第一種好一點也可以 - ROW_NUMBER() OVER(PARTITION BY Num ORDER BY Id)也是因為如此，在進行PARTITION BY Num後其真實順序會變**/
SELECT DISTINCT Num AS ConsecutiveNums
FROM ( SELECT Id,
Num,
(ROW_NUMBER() OVER(ORDER BY Id) - ROW_NUMBER() OVER(PARTITION BY Num ORDER BY Id)) AS tempcount
FROM Logs
) AS temp
GROUP BY Num, tempcount
HAVING COUNT(Id) >= 3
八、603. 連續空餘座位
1.題目描述
表: Cinema
+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id | int |
| free | bool |
+-------------+------+
Seat_id是該表的自動遞增主鍵列。
該表的每一行表示第i個座位是否空閒。1表示空閒，0表示被佔用。
編寫一個SQL查詢來報告電影院所有連續可用的座位。
返回按 seat_id 升序排序 的結果表。
測試用例的生成使得兩個以上的座位連續可用。
查詢結果格式如下所示。
示例 1:
輸入:
Cinema 表:
+---------+------+
| seat_id | free |
+---------+------+
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+---------+------+
輸出:
+---------+
| seat_id |
+---------+
| 3 |
| 4 |
| 5 |
+---------+
2.解題思路
又是連續問題，這裡相當於問題七的簡化，我們依然是根據其核心結論即： 如果一個num連續出現時，那麼它出現的[真實序列]-它出現的次數一定是個定值。因此思路如下：
1）用WITH AS 構造輔助臨時表temp，透過ROW_NUMBER OVER() 排序得出其出現次數
2）真實序列使用seat_id即可，因為seat_id都是從1開始不會出現從0開始得情況
3）根據temp表 我們使用group by 來對相減之後的定值k 分組，當相同的k有兩個及兩個以上時說明他是連續的
with temp as (
select seat_id, (seat_id- row_number() over()) as k
from (
select seat_id
from cinema
where free = 1
) t
)
temp表:
| seat_id | free | rn | k |
|---------|------|------|------|
| 1 | 1 | 1 | 0 |
| 3 | 1 | 2 | 1 |
| 4 | 1 | 3 | 1 |
| 5 | 1 | 4 | 1 |
| 8 | 1 | 5 | 3 |
| 9 | 1 | 6 | 3 |
3.程式碼實現
# Write your MySQL query statement below
/**SELECT DISTINCT c1.seat_id
FROM Cinema c1
JOIN Cinema c2
ON ABS(c1.seat_id - c2.seat_id) = 1 AND c1.free = 1 AND c2.free = 1
ORDER BY c1.seat_id**/
#使用視窗函式row_number over
WITH T
AS (
SELECT
seat_id,
(seat_id - ROW_NUMBER() OVER(ORDER BY seat_id)) AS k
FROM Cinema
WHERE free = 1
)
SELECT seat_id
FROM T
WHERE k IN (
SELECT
k
FROM T
GROUP BY k
HAVING COUNT(seat_id ) >= 2
)