数据准备
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)
;