Oracle数据库动态实现航班紧急出口座位组占用校验需求

核心需求 #

识别航班紧急出口行的座位，确保每个座位组（如ABC、HJK等）至少有一个座位被占用，未满足则输出警告。

方案一：基于配置表的动态分组（推荐） #

通过配置表存储不同航班、不同行的座位组定义，彻底解决硬编码适配性问题。

1. 创建座位组配置表 #

CREATE TABLE flight_seat_groups (
    flight_id VARCHAR2(20),
    seat_row VARCHAR2(5),
    group_code VARCHAR2(10),
    seat_cols VARCHAR2(10),
    PRIMARY KEY (flight_id, seat_row, group_code)
);

可根据实际航班座位布局插入配置数据，例如：

-- 示例：CA1234航班10号紧急出口行的座位组配置
INSERT INTO flight_seat_groups VALUES('CA1234', '10', '左侧组', 'ABC');
INSERT INTO flight_seat_groups VALUES('CA1234', '10', '中间组', 'DEF');
INSERT INTO flight_seat_groups VALUES('CA1234', '10', '右侧组', 'HJK');

2. 校验查询SQL #

WITH emergency_seats AS (
    SELECT 
        s.flight_id,
        s.seat_row,
        g.group_code,
        s.is_occupied
    FROM seats s
    -- 关联座位组配置
    JOIN flight_seat_groups g 
        ON s.flight_id = g.flight_id 
        AND s.seat_row = g.seat_row
        AND INSTR(g.seat_cols, s.seat_col) > 0
    -- 关联紧急出口行标记
    JOIN emergency_exit_rows e 
        ON s.flight_id = e.flight_id 
        AND s.seat_row = e.seat_row
),
group_check AS (
    SELECT 
        flight_id,
        seat_row,
        group_code,
        -- 每组只要有一个占用座位，MAX值为1
        MAX(is_occupied) AS has_occupied
    FROM emergency_seats
    GROUP BY flight_id, seat_row, group_code
)
SELECT 
    flight_id,
    seat_row,
    group_code,
    '警告：该座位组无已占用座位' AS warning_info
FROM group_check
WHERE has_occupied = 0;

方案二：基于规则的动态分组 #

若无需配置表，可通过座位列的通用规则自动分组（适合座位布局标准化的场景），例如跳过G座的常见布局：

WITH emergency_seats AS (
    SELECT 
        flight_id,
        seat_row,
        seat_col,
        is_occupied,
        -- 按左右中逻辑动态分组
        CASE 
            WHEN seat_col BETWEEN 'A' AND 'C' THEN '左侧组'
            WHEN seat_col BETWEEN 'D' AND 'F' THEN '中间组'
            WHEN seat_col BETWEEN 'H' AND 'K' THEN '右侧组'
        END AS group_code
    FROM seats
    -- 筛选紧急出口行
    WHERE seat_row IN (
        SELECT seat_row 
        FROM emergency_exit_rows 
        WHERE flight_id = seats.flight_id
    )
),
group_check AS (
    SELECT 
        flight_id,
        seat_row,
        group_code,
        MAX(is_occupied) AS has_occupied
    FROM emergency_seats
    GROUP BY flight_id, seat_row, group_code
)
SELECT 
    flight_id,
    seat_row,
    group_code,
    '警告：该座位组无已占用座位' AS warning_info
FROM group_check
WHERE has_occupied = 0;

方案对比 #

• 配置表方案：完全适配任意航班的座位组定义，维护灵活，但需提前配置数据。
• 规则分组方案：无需额外配置，实现简单，但仅适用于符合预设规则的座位布局。

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