2021-04-23

连续空余座位

需求：编写一个 SQL 查询，获取所有空余座位，并将它们按照 seat_id 排序

展示效果：

seat_id
3
4
5

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

注意：

• seat_id 字段是一个自增的整数，free 字段是布尔类型（‘1’ 表示空余， ‘0’ 表示已被占据）。
• 连续空余座位的定义是大于等于 2 个连续空余的座位。

最终SQL:

select	
	a.seat_id
from
	28_cinema a,
	28_cinema b
where
	abs(a.sert_id - b.seat_id) = 1 and a.free = true and b.free = true
order by
	a.seat_id