几个朋友来到电影院的售票处,准备预约连续空余座位。
你能利用表 cinema ,帮他们写一个查询语句,获取所有空余座位,并将它们按照 seat_id 排序后返回吗?
seat_id | free |
---|---|
1 | 1 |
2 | 0 |
3 | 1 |
4 | 1 |
5 | 1 |
对于如上样例,你的查询语句应该返回如下结果。
seat_id |
---|
3 |
4 |
5 |
注意:
seat_id 字段是一个自增的整数,free 字段是布尔类型(‘1’ 表示空余, ‘0’ 表示已被占据)。
连续空余座位的定义是大于等于 2 个连续空余的座位。
--建表语句
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')
--答案1
--利用窗口函数
select
seat_id
from
(select
seat_id,
free,
sum(free) over(partition by (seat_id-mark)) as mk
from
(select
seat_id,
free,
row_number() over() as mark
from
cinema where free<>0)
a)
b where mk >=2
--答案2
--笛卡尔积
select
distinct a.seat_id as seat_id
from
cinema a,
cinema b where
abs(a.seat_id-b.seat_id)=1 and
a.free=1 and
b.free=1 order by
a.seat_id