几个朋友来到电影院的售票处,准备预约连续空余座位。
你能利用表 cinema ,帮他们写一个查询语句,获取所有空余座位,并将它们按照 seat_id 排序后返回吗?
| seat_id | free |
|---------|------|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
对于如上样例,你的查询语句应该返回如下结果。
| seat_id |
|---------|
| 3 |
| 4 |
| 5 |
注意:
seat_id 字段是一个自增的整数,free 字段是布尔类型('1' 表示空余, '0' 表示已被占据)。
+ 连续空余座位的定义是大于等于 2 个连续空余的座位。
#插入代码
DROP TABLE IF EXISTS cinema;
CREATE TABLE cinema
(
seat_id INT,
free INT
);
INSERT INTO cinema VALUES(1, 1);
INSERT INTO cinema VALUES(2, 0);
INSERT INTO cinema VALUES(3, 1);
INSERT INTO cinema VALUES(4, 1);
INSERT INTO cinema VALUES(5, 1);
#正确答案
SELECT DISTINCT a.seat_id
FROM cinema a,cinema b
WHERE (a.seat_id-1=b.seat_id OR a.seat_id+1=b.seat_id) AND (a.free=1 AND b.free=1)
ORDER BY a.seat_id;
#展示
#错误答案
SELECT c1.seat_id,c2. seat_id,c3. seat_id
FROM cinema c1,cinema c2,cinema c3
WHERE c1.free ='1' AND c2.free ='1' AND c3.free ='1' AND c1. seat_id+1=c2. seat_id
AND c2. seat_id+1=c3. seat_id
#错误