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 seats consecutively available.
Solution 1:
# Write your MySQL query statement below
SELECT seat_id
FROM cinema
WHERE
free = 1
AND
(
seat_id + 1 IN (SELECT seat_id FROM cinema WHERE free = 1)
OR
seat_id - 1 IN (SELECT seat_id FROM cinema WHERE free = 1)
)
Solution 2:
# Write your MySQL query statement below
SELECT DISTINCT c1.seat_id
FROM cinema as c1, cinema as c2
WHERE c1.free = 1 and c2.free = 1 and (c1.seat_id - 1 = c2.seat_id or c1.seat_id + 1 = c2.seat_id);
Solution 3:
# Write your MySQL query statement below
SELECT DISTINCT c1.seat_id
FROM cinema as c1
JOIN cinema as c2
ON (c1.seat_id - 1 = c2.seat_id or c1.seat_id + 1 = c2.seat_id)
AND c1.free = 1
AND c2.free = 1;
Summary:
- nothing to say
LeetCode: 603. Consecutive Available Seats
近期评论