SQL专项复习（窗口函数）——习题篇01

原文链接：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
)