LeetCode 603.连续空余座位

数据准备

drop table Cinema;

Create table If Not Exists Cinema (seat_id int primary key auto_increment, free bool);
Truncate table Cinema;
insert into Cinema (seat_id, free) values ('1', '1');
insert into Cinema (seat_id, free) values ('2', '0');
insert into Cinema (seat_id, free) values ('3', '1');
insert into Cinema (seat_id, free) values ('4', '1');
insert into Cinema (seat_id, free) values ('5', '1');

需求

编写一个SQL查询来报告电影院所有连续可用的座位。返回按 seat_id 升序排序的结果表。测试用例的生成使得两个以上的座位连续可用。

输入

输出

with t1 as (
    select *,
       coalesce(lag(free,1) over (),-1) rn1
    from Cinema
    order by seat_id
)
select seat_id
from t1
where (free-rn1) in (0,1)
;