SQL练习00006

几个朋友来到电影院的售票处，准备预约连续空余座位。
你能利用表 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