如何在MySQL中查询连续3个空置座位的记录

已知movie表结构及数据如下：

其中id为座位号，status=0表示空置，status=1表示已占用。以下是几种实现筛选连续3个空置座位的SQL方案：

方法1：自连接法 #

通过自连接匹配连续三个座位的id，再通过UNION合并这三个座位的记录：

SELECT m1.id, m1.name, m1.status
FROM movie m1
JOIN movie m2 ON m2.id = m1.id + 1 AND m2.status = 0 AND m2.name = m1.name
JOIN movie m3 ON m3.id = m1.id + 2 AND m3.status = 0 AND m3.name = m1.name
WHERE m1.status = 0
UNION
SELECT m2.id, m2.name, m2.status
FROM movie m1
JOIN movie m2 ON m2.id = m1.id + 1 AND m2.status = 0 AND m2.name = m1.name
JOIN movie m3 ON m3.id = m1.id + 2 AND m3.status = 0 AND m3.name = m1.name
WHERE m1.status = 0
UNION
SELECT m3.id, m3.name, m3.status
FROM movie m1
JOIN movie m2 ON m2.id = m1.id + 1 AND m2.status = 0 AND m2.name = m1.name
JOIN movie m3 ON m3.id = m1.id + 2 AND m3.status = 0 AND m3.name = m1.name
WHERE m1.status = 0
ORDER BY id;

方法2：窗口函数（LAG/LEAD） #

利用LEAD和LAG函数获取前后座位的状态，判断当前座位是否属于连续三个空置的区间：

WITH consecutive_seats AS (
    SELECT 
        id,
        name,
        status,
        LEAD(status, 1) OVER (PARTITION BY name ORDER BY id) AS next_status,
        LEAD(status, 2) OVER (PARTITION BY name ORDER BY id) AS next_next_status,
        LAG(status, 1) OVER (PARTITION BY name ORDER BY id) AS prev_status,
        LAG(status, 2) OVER (PARTITION BY name ORDER BY id) AS prev_prev_status
    FROM movie
    WHERE status = 0
)
SELECT id, name, status
FROM consecutive_seats
WHERE 
    (status = 0 AND next_status = 0 AND next_next_status = 0)
    OR (status = 0 AND prev_status = 0 AND next_status = 0)
    OR (status = 0 AND prev_status = 0 AND prev_prev_status = 0)
ORDER BY id;

方法3：分组标识法 #

通过计算分组标识，将连续的空置座位归为同一组，再筛选出座位数≥3的组：

WITH seat_groups AS (
    SELECT 
        id,
        name,
        status,
        id - ROW_NUMBER() OVER (PARTITION BY name, status ORDER BY id) AS group_id
    FROM movie
    WHERE status = 0
),
group_counts AS (
    SELECT group_id, name, COUNT(*) AS seat_count
    FROM seat_groups
    GROUP BY group_id, name
    HAVING COUNT(*) >= 3
)
SELECT sg.id, sg.name, sg.status
FROM seat_groups sg
JOIN group_counts gc ON sg.group_id = gc.group_id AND sg.name = gc.name
ORDER BY sg.id;

以上三种方法都能得到目标结果，其中：

• 自连接法逻辑直观，适合数据量较小的场景；
• 窗口函数法灵活性高，便于扩展到连续N个座位的需求；
• 分组标识法适合批量筛选任意连续数量的空置座位。

内容的提问来源于stack exchange，提问作者Shruti Saxena