解题报告-603. Consecutive Available Seats
Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seats order by the seat_id using the following cinema table?
| seat_id | free |
|---------|------|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
Your query should return the following result for the sample case above.
| seat_id |
|---------|
| 3 |
| 4 |
| 5 |
Note:
The seat_id is an auto increment int, and free is bool ('1' means free, and '0' means occupied.).
Consecutive available seats are more than 2(inclusive) seats consecutively available.
因为只有一张表,所以需要join才能拿到更多信息
通过abs(a.seat_id - b.seat_id) = 1 找到相邻的座位
通过a.free = true and b.free = true 检查是否符合要求
最后通过distinct 和 order by 顺序输出
select distinct a.seat_id
from cinema a join cinema b
on abs(a.seat_id - b.seat_id) = 1
and a.free = true
and b.free = true
order by a.seat_id