47 连续空余的座位

[astak16]

题目

题目链接：连续空余的座位

seat_id是该表的自动递增主键列。

每一行表示第 i 个座位是否空闲。1 表示空闲， 0 表示被占用。

编写一个 SQL 查询电影院所有连续可用的座位（两个或者两个以上即为连续可用的座位）。返回按  seat_id 升序排序  的结果表。

create table cinema (
	seat_id int primary key auto_increment,
	free bool
);

insert into cinema (seat_id, free) values
('1', '1'),
('2', '0'),
('3', '1'),
('4', '1'),
('5', '1');

SQL：方法一

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;

解析

1. 因为需要最后需要查出空闲的连续的座位号，所以就需要连续的两个座位号 seat_id 进行比较，而现在只有一张表，需要自己连自己。

   select * from cinema c1 join cinema c2;

2. join 连接后的结果是笛卡尔积，所以需要有筛选条件，筛选条件是

   1. abs(c1.seat_id - c2.seat_id) = 1 因为，两个座位号相连，座位号 seat_id 相减为 1 的就是相连的座位号。这里使用 abs() 取绝对值，就无需考虑 c1 、 c2 表顺序的问题了。
   2. c1.free = 1 and c2.free = 1 ，因为 free = 1 表示这个座位空闲
   3. 用 and 连接 a 和 b

3. 使用 distinct 去掉重复的 seat_id

SQL：方法二

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
);

解析

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

SQL：方法三

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
);

解析

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

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