【LeetCode-SQL】603. 连续空余座位

一、题目

表: 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       |
+---------+

二、解决

1、join

思路：

只有一张表，需要通过seat_id自己连自己，条件是abs(c1.seat_id - c2.seat_id) = 1。
代码：

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;

2、row_number() over()

思路：

S1：使用窗口函数 row_number 对 cinema 进行排序，seat_id - row_number() over() as k ，如果座位连续，这组 k 值应该是相等的。
S2：将第一步和第二步的结果作为临时表 temp，将临时表 temp 按照 k 进行分组查询，并且筛选出大于 2 的 k 值
S3：从临时表 temp 中查询出 seat_id ，筛选出 k 值在第四步中的值

代码：

with temp as (
	select
		seat_id,
		seat_id - row_number() over() as k
	from cinema where free = 1
)
select seat_id from temp where k in (
	select k from temp group by k having count(*) >= 2
);

3、变量

思路：

方法二中使用窗口函数的地方替换成了 case … when … then … end 的形式

在运行第一条数据时不会，不会进入第一个 when 子句中，它会进入第二个 when 子句。第二个 when 子句的作用是对 @pre_free 和 @pre_seat_id 进行初始化
当运行第二条数据时，就会进入第一个 when 子句，不会进入第二个 when 子句了。第一个 when 子句中会判断 @pre_free (上一数据 free)和 free (当前的 free) 是否相等，并且 free 是等于 1 的，输出 @pre_seat_id (上一条数据的 seat_id)

代码：

with temp as (
	select
		seat_id,
		case
			when @pre_free = free and free = 1 then @pre_seat_id
			when @pre_free:=free then @pre_seat_id:=seat_id
		end as k
	from cinema, (select @pre_free:=null, @pre_seat_id:=null) init
)
select seat_id from temp where k in (
	select k from temp group by k having count(*) >= 2
);

三、参考

1、三种方法——解连续空余的座位（笛卡尔积/窗口函数/case when …）