字节面试SQL-寻找电影院连续3个空闲座位

数据仓库晨曦

发布于 2026-06-23 17:26:35

480

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目

有电影院座位预定数据如下，现有3个朋友要一同去看电影，按照要求找到符合要求的座位。

问题一：找到连续空闲超过3个的座位，并给出开始和结束座位号； 期望结果

+-------------+-----------+
| start_seat  | end_seat  |
+-------------+-----------+
| 3           | 5         |
| 7           | 12        |
| 15          | 19        |
+-------------+-----------+

问题二：找到同排中连续空闲超过3个的座位，给出排号、开始和结束座位号； 期望结果

+---------+-------------+-----------+
| row_id  | start_seat  | end_seat  |
+---------+-------------+-----------+
| 1       | 3           | 5         |
| 1       | 7           | 10        |
| 2       | 15          | 19        |
+---------+-------------+-----------+

样例数据

+---------+----------+-------+
| row_id  | seat_id  | free  |
+---------+----------+-------+
| 1       | 1        | 1     |
| 1       | 2        | 0     |
| 1       | 3        | 1     |
| 1       | 4        | 1     |
| 1       | 5        | 1     |
| 1       | 6        | 0     |
| 1       | 7        | 1     |
| 1       | 8        | 1     |
| 1       | 9        | 1     |
| 1       | 10       | 1     |
| 2       | 11       | 1     |
| 2       | 12       | 1     |
| 2       | 13       | 0     |
| 2       | 14       | 0     |
| 2       | 15       | 1     |
| 2       | 16       | 1     |
| 2       | 17       | 1     |
| 2       | 18       | 1     |
| 2       | 19       | 1     |
| 2       | 20       | 0     |
| 3       | 21       | 0     |
| 3       | 22       | 1     |
| 3       | 23       | 1     |
| 3       | 24       | 0     |
| 3       | 25       | 1     |
| 3       | 26       | 0     |
| 3       | 27       | 1     |
| 3       | 28       | 1     |
| 3       | 29       | 0     |
| 3       | 30       | 1     |
+---------+----------+-------+

二、分析

本题两问都是连续问题的查询，差别在问题二添加了一层分组，如果能理清楚问题一，问题二不难，直接做问题难度略大一点，整体属于连续问题的入门款

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

第1问：找全局连续3个空位

不考虑排的限制，只要座位编号连续且空闲 ≥ 3 即可。

先获得连续数据的分组ID，使用差值法，由于座位号原本连续，直接使用seat_id减去空闲座位的排序即可

执行SQL

SELECT seat_id,
       seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
FROM t7_cinema
WHERE free = 1

执行结果

+----------+------+
| seat_id  | grp  |
+----------+------+
| 1        | 0    |
| 3        | 1    |
| 4        | 1    |
| 5        | 1    |
| 7        | 2    |
| 8        | 2    |
| 9        | 2    |
| 10       | 2    |
| 11       | 2    |
| 12       | 2    |
| 15       | 4    |
| 16       | 4    |
| 17       | 4    |
| 18       | 4    |
| 19       | 4    |
| 22       | 6    |
| 23       | 6    |
| 25       | 7    |
| 27       | 8    |
| 28       | 8    |
| 30       | 9    |
+----------+------+
21 rows selected (0.301 seconds)

根据分组ID grp进行分组统计，行数>=3 即为符合条件的分组，然后取得组内的最小值（开始座位ID），最大值（结束座位ID）

执行SQL

SELECT MIN(seat_id) AS start_seat,
       MAX(seat_id) AS end_seat
FROM (SELECT seat_id,
             seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
      FROM t7_cinema
      WHERE free = 1) t
GROUP BY grp
HAVING COUNT(*) >= 3;

结果

+-------------+-----------+
| start_seat  | end_seat  |
+-------------+-----------+
| 3           | 5         |
| 7           | 12        |
| 15          | 19        |
+-------------+-----------+
3 rows selected (0.302 seconds)(数据仓库技术dwsql.com)

第2问：找同一排内连续3个空位

| 大家需要坐在一起，跨排座位ID连续，但是实际不连续，所以要求在同一排。

获得分组ID，这里row_number()需要按照row_id 分组

执行SQL

SELECT row_id,
       seat_id,
       seat_id - ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY seat_id) AS grp
FROM t7_cinema
WHERE free = 1

执行结果

+---------+----------+------+
| row_id  | seat_id  | grp  |
+---------+----------+------+
| 1       | 1        | 0    |
| 1       | 3        | 1    |
| 1       | 4        | 1    |
| 1       | 5        | 1    |
| 1       | 7        | 2    |
| 1       | 8        | 2    |
| 1       | 9        | 2    |
| 1       | 10       | 2    |
| 2       | 11       | 10   |
| 2       | 12       | 10   |
| 2       | 15       | 12   |
| 2       | 16       | 12   |
| 2       | 17       | 12   |
| 2       | 18       | 12   |
| 2       | 19       | 12   |
| 3       | 22       | 21   |
| 3       | 23       | 21   |
| 3       | 25       | 22   |
| 3       | 27       | 23   |
| 3       | 28       | 23   |
| 3       | 30       | 24   |
+---------+----------+------+
21 rows selected (8.806 seconds)dwsql.com

根据row_id和grp进行分组（这里如果不是要展示座位所属排，可以仅根据grp进行分组，因为在上一步row_number中已经包含排的信息进行分组了），统计组内座位个数，限制3个以上，并取最大最小值）

执行SQL

SELECT row_id,
       MIN(seat_id) AS start_seat,
       MAX(seat_id) AS end_seat
FROM (SELECT row_id,
             seat_id,
             seat_id - ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY seat_id) AS grp
      FROM t7_cinema
      WHERE free = 1) t
GROUP BY row_id, grp
HAVING COUNT(*) >= 3;

执行结果

+---------+-------------+-----------+
| row_id  | start_seat  | end_seat  |
+---------+-------------+-----------+
| 1       | 3           | 5         |
| 1       | 7           | 10        |
| 2       | 15          | 19        |
+---------+-------------+-----------+
3 rows selected (0.597 seconds)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-05-26，如有侵权请联系 cloudcommunity@tencent.com 删除

数据