如何在数据库表中查询指定数量的同排连续可用座位

你原有SQL无法返回预期结果，核心问题有两个：

• 连表校验连续座位时没有加同排匹配条件a.row = b.row，会跨排统计座位，导致连续判断逻辑失效
• 内层子查询会返回多个符合条件的起始座位值，外层直接用seat >= 子查询结果的写法会出现非预期匹配，也没有按排维度做分组过滤

下面给出两种可直接落地的实现方案，均支持自定义查询连续座位的数量x。

方案1：窗口函数实现（推荐，适合大数据量场景） #

支持MySQL 8.0+、PostgreSQL等所有兼容窗口函数的数据库，查询效率远高于自连接写法，适合座位数据量大的生产场景使用。
核心逻辑：给同一排内的可用座位按座位号排序，用「座位号 - 排序行号」生成连续座位的分组标识，同一个标识下的座位必然是连续的，只要统计分组内座位数大于等于要求的x值，就能筛选出符合要求的连续座位段。
以查询连续2个可用座位为例，SQL如下：

WITH available_seats AS (
    SELECT 
        `row`,
        seat,
        -- 同排内可用座位按座号升序排序打行号
        ROW_NUMBER() OVER (PARTITION BY `row` ORDER BY seat) AS rn,
        -- 连续座位生成相同group_id，非连续座位group_id不同
        seat - ROW_NUMBER() OVER (PARTITION BY `row` ORDER BY seat) AS group_id
    FROM seats
    WHERE status = 1
),
consecutive_groups AS (
    SELECT 
        `row`,
        group_id,
        MIN(seat) AS start_seat,
        MAX(seat) AS end_seat,
        COUNT(*) AS consecutive_len
    FROM available_seats
    GROUP BY `row`, group_id
    -- 调整此处数字即可修改要查询的连续座位数，例如找3个连续座位改为 >=3
    HAVING COUNT(*) >= 2
)
-- 查询所有符合要求的连续座位段起止信息
SELECT * FROM consecutive_groups;

如果需要返回连续段内的每一个具体座位，替换最后一段查询即可：

SELECT s.*
FROM seats s
JOIN consecutive_groups cg
ON s.`row` = cg.`row`
AND s.seat BETWEEN cg.start_seat AND cg.end_seat
ORDER BY s.`row`, s.seat;

方案2：旧版MySQL兼容实现（无窗口函数场景） #

如果使用不支持窗口函数的MySQL 5.x及更早版本，可以用自连接计数的方式实现，逻辑为：从每个可用座位开始，向后查找同排内连续x个座位，如果x个座位全部为可用状态，即命中符合要求的连续段。
以查询连续2个可用座位为例，SQL如下：

SELECT 
  a.`row`,
  GROUP_CONCAT(b.seat ORDER BY b.seat) AS consecutive_seats,
  MIN(b.seat) AS start_seat
FROM seats a
JOIN seats b 
  ON a.`row` = b.`row` 
  -- 调整a.seat + 1的数值即可修改连续座位数，例如找3个连续座位改为 a.seat + 2
  AND b.seat BETWEEN a.seat AND a.seat + 1
  AND b.status = 1
WHERE a.status = 1
GROUP BY a.`row`, a.seat
-- 调整此处数字匹配要查询的连续座位数，例如找3个连续座位改为 =3
HAVING COUNT(b.seat) = 2;

优化注意事项 #

• seat字段建议使用整数类型存储，若当前为带前导零的字符串类型，建议提前转为整数，避免数值计算、排序时出现隐式转换导致的结果错误
• 大数据量场景下建议为(row, seat, status)建立联合索引，可将查询性能提升数倍
• 订票场景需注意并发超卖问题，查询到可用座位后，需要在事务中加行锁更新座位状态后再生成订单，不能直接使用查询结果下单

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