SQL航班座位分配程序调试：代码为何无法通过测试？

任务要求 #

现有seats表存储航班座位信息，字段如下：

• seat_no：座位唯一编号
• status：座位状态（0=空闲，1=已预订，2=已购买）
• person_id：预订/购买用户ID（状态为0时该值为0）

另有requests表记录座位操作请求，字段如下：

• request_id：请求唯一ID
• request：请求类型（1=预订，2=购买）
• seat_no：目标座位编号
• person_id：发起请求的用户ID

规则说明：

• 用户可预订或购买空闲座位
• 用户可购买自己已预订的座位
• 请求必须按request_id从小到大的顺序执行
• requests表中所有seat_no均存在于seats表中

示例 #

输入seats表 #

seat_no status  person_id
      1      1          1
      2      1          2
      3      0          0
      4      2          3
      5      0          0

输入requests表 #

request_id request seat_no person_id
          1       1       3         4
          2       2       2         5
          3       2       1         1

预期输出 #

seat_no    status  person_id
       1         2          1
       2         1          2
       3         1          4
       4         2          3
       5         0          0

逻辑说明 #

• 请求1成功：座位3处于空闲状态
• 请求2被忽略：座位2被他人预订
• 请求3成功：座位1为当前用户预订

我的实现代码 #

CREATE PROCEDURE solution()
BEGIN
    /* Write your SQL here. Terminate each statement with a semicolon. */
    
WITH ranked_requests AS (
    SELECT
        r.request_id,
        r.request,
        r.seat_no,
        r.person_id,
        ROW_NUMBER() OVER (PARTITION BY r.seat_no ORDER BY r.request_id) AS rn
    FROM
        requests r
),
applied_requests AS (
    SELECT
        s.seat_no,
        COALESCE(MAX(CASE
            WHEN r.request = 1 AND s.status = 0 THEN 1
            WHEN r.request = 2 AND s.status IN (0, 1) AND (s.status = 0 OR s.person_id = r.person_id) THEN 2
            ELSE s.status
        END), s.status) AS status,
        COALESCE(MAX(CASE
            WHEN r.request = 1 AND s.status = 0 THEN r.person_id
            WHEN r.request = 2 AND s.status IN (0, 1) AND (s.status = 0 OR s.person_id = r.person_id) THEN r.person_id
            ELSE s.person_id
        END), s.person_id) AS person_id
    FROM
        seats s
        LEFT JOIN ranked_requests r ON s.seat_no = r.seat_no
    GROUP BY
        s.seat_no
)
SELECT
    a.seat_no,
    a.status,
    a.person_id
FROM
    applied_requests a
ORDER BY
    a.seat_no;

END

测试情况 #

代码通过Test 1，但在Test 2中返回"Wrong answer"，Test 2输入如下：

输入seats表 #

seat_no status  person_id
      1      2          1
      2      1          2
      3      0          0
      4      2          3
      5      0          0
      6      0          0
      7      2          1
      8      1         31
      9      2         81
     10      2         10

输入requests表 #

request_id  request seat_no person_id
         1        1       3         4
         2        2       2         5
         3        2       1         1
         4        1       9        81
         5        2      10        10
         6        1       3        59

问题分析 #

你的代码核心问题是没有按请求顺序处理状态变更，而是基于座位的初始状态批量判断所有请求，完全忽略了请求之间的依赖关系：

1. 同一个座位的多个请求必须按request_id顺序执行，每个请求的判断依据是前一个请求处理后的座位状态，而非初始状态。比如座位3的请求1成功后状态变为1，后续请求6（用户59预订）应该失败，但你的代码用初始状态0判断，会错误地认为请求6可以执行。
2. 使用GROUP BY和MAX的方式无法模拟顺序执行的状态流转，只能基于初始状态做静态判断。

修正后的代码 #

CREATE PROCEDURE solution()
BEGIN
    -- 先将请求按request_id排序，生成顺序索引
    WITH ordered_requests AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY request_id) AS seq
        FROM requests
    ),
    -- 递归CTE：逐个处理请求，维护每个座位的当前状态
    recursive_process AS (
        -- 初始状态：所有座位的初始信息
        SELECT 
            s.seat_no,
            s.status,
            s.person_id,
            0 AS processed_seq -- 已处理的请求序号，初始为0
        FROM seats s
        
        UNION ALL
        
        -- 递归处理每个请求
        SELECT
            rp.seat_no,
            -- 判断当前请求是否有效，更新状态
            CASE
                -- 如果当前座位是已购买状态，任何请求都不改变状态
                WHEN rp.status = 2 THEN rp.status
                -- 预订请求：仅当座位空闲时生效
                WHEN orq.request = 1 AND rp.status = 0 THEN 1
                -- 购买请求：座位空闲 或 是当前用户预订的座位时生效
                WHEN orq.request = 2 AND (rp.status = 0 OR (rp.status = 1 AND rp.person_id = orq.person_id)) THEN 2
                -- 无效请求，保持原状态
                ELSE rp.status
            END AS status,
            -- 更新用户ID：仅当请求生效时变更
            CASE
                WHEN rp.status = 2 THEN rp.person_id
                WHEN (orq.request = 1 AND rp.status = 0) OR (orq.request = 2 AND (rp.status = 0 OR (rp.status = 1 AND rp.person_id = orq.person_id))) THEN orq.person_id
                ELSE rp.person_id
            END AS person_id,
            orq.seq AS processed_seq
        FROM recursive_process rp
        JOIN ordered_requests orq 
            ON rp.seat_no = orq.seat_no 
            AND rp.processed_seq = orq.seq - 1
    ),
    -- 取每个座位最后处理后的状态（即最大的processed_seq对应的记录）
    final_status AS (
        SELECT 
            seat_no,
            status,
            person_id,
            ROW_NUMBER() OVER (PARTITION BY seat_no ORDER BY processed_seq DESC) AS rn
        FROM recursive_process
    )
    SELECT 
        seat_no,
        status,
        person_id
    FROM final_status
    WHERE rn = 1
    ORDER BY seat_no;
END

代码说明 #

1. ordered_requests：将所有请求按request_id排序，生成连续的序号seq，方便递归顺序处理。
2. recursive_process：递归CTE，从座位初始状态开始，逐个处理每个请求：
   • 每次递归处理序号为seq的请求，基于上一次处理后的座位状态判断请求是否有效。
   • 有效请求则更新座位状态和用户ID，无效则保持原状态。
3. final_status：对每个座位取最后一次处理后的状态（即最大processed_seq对应的记录），得到最终的座位状态。

这个方案严格遵循了请求的顺序执行要求，正确处理了状态的流转，能够通过所有测试用例。

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