sql notes: leetcode#603 consecutive available seats

Problem

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?

Your query should return the following result for the sample case above.

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.

Analysis

Cross join 2 cinema tables to iterate all possible seat pairs. Output first seat of seat pairs when both seats are free and the two seats are consecutive:

1

SELECT c1.seat_id FROM cinema AS c1, cinema AS c2 WHERE c1.free = 1 AND c2.free = 1 AND (c2.seat_id = c1.seat_id + 1 OR c2.seat_id = c1.seat_id -1);

The query will output seats in the middle of consecutive available seats twice. We need to use DISTINCT to prevent duplicates:

1

SELECT DISTINCT c1.seat_id FROM cinema AS c1, cinema AS c2 WHERE c1.free = 1 AND c2.free = 1 AND (c2.seat_id = c1.seat_id + 1 OR c2.seat_id = c1.seat_id -1);

At last, order by seat_id:

1

SELECT DISTINCT c1.seat_id FROM cinema AS c1, cinema AS c2 WHERE c1.free = 1 AND c2.free = 1 AND (c2.seat_id = c1.seat_id + 1 OR c2.seat_id = c1.seat_id -1) ORDER BY c1.seat_id;

Solution

1

SELECT DISTINCT c1.seat_id FROM cinema AS c1, cinema AS c2 WHERE c1.free = 1 AND c2.free = 1 AND (c2.seat_id = c1.seat_id + 1 OR c2.seat_id = c1.seat_id -1) ORDER BY c1.seat_id;

Link

603. Consecutive Available Seats
(中文版) SQL 笔记: Leetcode#603 Consecutive Available Seats