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. solution: 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 ; note: join full join inner join right join left join
转载于:https://my.oschina.net/u/2277632/blog/1591754