1 题目描述
表: Cinema
+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id | int |
| free | bool |
+-------------+------+
Seat_id 是该表的自动递增主键列
在 PostgreSQL 中, free 存储为整数. 请使用 ::boolean 将其转换为布尔格式
该表的每一行表示第 i 个座位是否空闲. 1 表示空闲, 0 表示被占用
查找电影院所有连续可用的座位
返回按 seat_id 升序排序 的结果表
测试用例的生成使得两个以上的座位连续可用
2 测试用例
输入:
Cinema 表:
+---------+------+
| seat_id | free |
+---------+------+
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+---------+------+
输出:
+---------+
| seat_id |
+---------+
| 3 |
| 4 |
| 5 |
+---------+
3 解题思路
- 使用
join让Cinema关联自己
select a.seat_id, a.free, b.seat_id, b.free
from Cinema a join Cinema b
查询结果
+-------+----+-------+----+
|seat_id|free|seat_id|free|
+-------+----+-------+----+
|5 |1 |1 |1 |
|4 |1 |1 |1 |
|3 |1 |1 |1 |
|2 |0 |1 |1 |
|1 |1 |1 |1 |
|5 |1 |2 |0 |
|4 |1 |2 |0 |
|3 |1 |2 |0 |
|2 |0 |2 |0 |
|1 |1 |2 |0 |
|5 |1 |3 |1 |
|4 |1 |3 |1 |
|3 |1 |3 |1 |
|2 |0 |3 |1 |
|1 |1 |3 |1 |
|5 |1 |4 |1 |
|4 |1 |4 |1 |
|3 |1 |4 |1 |
|2 |0 |4 |1 |
|1 |1 |4 |1 |
|5 |1 |5 |1 |
|4 |1 |5 |1 |
|3 |1 |5 |1 |
|2 |0 |5 |1 |
|1 |1 |5 |1 |
+-------+----+-------+----+
- 需要查找两个以上的座位是连续可用的, 意味着
Cinema a的seat_id和Cinema b的seat_id是挨着的, 且都是空闲的, 可以使用abs(a.seat_id - b.seat_id) = 1判断两个座位是相邻的, 使用a.free = true and b.free = true可以查找两个座位是空闲的
select a.seat_id, a.free, b.seat_id, b.free
from Cinema a
join Cinema b on abs(a.seat_id - b.seat_id) = 1 and a.free = true and b.free = true;
查询结果
+-------+----+-------+----+
|seat_id|free|seat_id|free|
+-------+----+-------+----+
|4 |1 |3 |1 |
|5 |1 |4 |1 |
|3 |1 |4 |1 |
|4 |1 |5 |1 |
+-------+----+-------+----+
- 步骤 2 的查询结果中, 第 1, 4 条数据对应的是同一个座位组合, 且查询结果只需要返回哪些座位是满足要求的, 不用返回座位组合, 使用
distinct a.seat_id对数据去重, 最后对seat_id进行升序排序
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;
查询结果
+-------+
|seat_id|
+-------+
|3 |
|4 |
|5 |
+-------+
