[LeetCode] 603. Consecutive Available Seats_Easy tag: SQL

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, 就是先得到seat_id 和所有seat_id对应的表格, 然后再选两个的差值是1的, 最后Distinct 并且 in order.

Code

SELECT DISTINCT c1.seat_id FROM cinema AS c1 JOIN cinema AS c2 WHERE abs(c1.seat_id - c2.seat_id) = 1 and c1.free = True and c2.free = True 
order by c1.seat_id