解题报告-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

转载于:https://www.cnblogs.com/ctrlzhang/p/10453208.html