如何在Oracle查询中验证航班应急出口排座位占用要求

需求背景 #

需要确保航班应急出口区域的每个子排（如示例中的4ABC、4DEF、5ABC、5DEF）至少有一个座位被占用，若存在子排无任何座位使用，需触发警告。现有查询已能筛选出应急出口的座位数据，要求在查询层面（如CASE语句）实现验证，无需使用HashMap。

解决方案 #

核心思路是先将座位归属到对应的子排分组，再通过聚合或窗口函数判断每个子排是否有已占用座位，最后用CASE生成警告信息。以下提供两种实现方式：

方式1：按子排分组输出状态（适合批量检查）

通过CTE筛选应急出口座位并提取子排标识，再分组聚合判断每个子排的占用情况：

WITH exit_seats AS (
    SELECT 
        FLIGHT_CARRIER,
        FLIGHT_NUMBER,
        flight_date,
        -- 提取子排标识，请根据实际座位号格式调整逻辑
        -- 示例：将4A/4B/4C归为4ABC子排，4D/4E/4F归为4DEF子排
        CONCAT(
            SUBSTR(SEAT_NUMBER, 1, REGEXP_INSTR(SEAT_NUMBER, '[A-Z]') - 1),
            CASE 
                WHEN SUBSTR(SEAT_NUMBER, REGEXP_INSTR(SEAT_NUMBER, '[A-Z]')) BETWEEN 'A' AND 'C' THEN 'ABC'
                WHEN SUBSTR(SEAT_NUMBER, REGEXP_INSTR(SEAT_NUMBER, '[A-Z]')) BETWEEN 'D' AND 'F' THEN 'DEF'
                ELSE 'UNKNOWN_SUBROW'
            END
        ) AS sub_row_id,
        availability_attribute
    FROM SEAT_ALLOC 
    WHERE AIRLINE ='LL'
      AND FLIGHT_CARRIER = 'LL'
      AND LOCATION_ATT LIKE  '%E%'
      AND FLIGHT_NUMBER='7893'
      AND flight_date=to_date('2021-10-10', 'YYYY-MM-DD')
)
SELECT 
    FLIGHT_CARRIER,
    FLIGHT_NUMBER,
    flight_date,
    sub_row_id,
    CASE 
        -- 替换'USED'为实际业务中表示座位已占用的属性值
        WHEN MAX(CASE WHEN availability_attribute = 'USED' THEN 1 ELSE 0 END) = 0 
        THEN '警告：该子排无任何座位被占用'
        ELSE '正常：子排已至少有一个座位被占用'
    END AS sub_row_status
FROM exit_seats
GROUP BY FLIGHT_CARRIER, FLIGHT_NUMBER, flight_date, sub_row_id;

方式2：保留座位明细并显示子排状态（适合关联座位数据查看）

使用窗口函数，在每条座位记录上标注其所属子排的状态：

SELECT 
    FLIGHT_CARRIER,
    FLIGHT_NUMBER,
    SEAT_NUMBER,
    PAX_ID,
    LOCATION_ATT,
    flight_date,
    int_row_pos,
    availability_attribute,
    CASE 
        WHEN MAX(CASE WHEN availability_attribute = 'USED' THEN 1 ELSE 0 END) OVER (PARTITION BY sub_row_id) = 0
        THEN '警告：该子排无任何座位被占用'
        ELSE '正常'
    END AS sub_row_status
FROM (
    SELECT 
        *,
        -- 同方式1的子排标识提取逻辑
        CONCAT(
            SUBSTR(SEAT_NUMBER, 1, REGEXP_INSTR(SEAT_NUMBER, '[A-Z]') - 1),
            CASE 
                WHEN SUBSTR(SEAT_NUMBER, REGEXP_INSTR(SEAT_NUMBER, '[A-Z]')) BETWEEN 'A' AND 'C' THEN 'ABC'
                WHEN SUBSTR(SEAT_NUMBER, REGEXP_INSTR(SEAT_NUMBER, '[A-Z]')) BETWEEN 'D' AND 'F' THEN 'DEF'
                ELSE 'UNKNOWN_SUBROW'
            END
        ) AS sub_row_id
    FROM SEAT_ALLOC 
    WHERE AIRLINE ='LL'
      AND FLIGHT_CARRIER = 'LL'
      AND LOCATION_ATT LIKE  '%E%'
      AND FLIGHT_NUMBER='7893'
      AND flight_date=to_date('2021-10-10', 'YYYY-MM-DD')
) t;

关键注意事项 #

1. 子排标识提取逻辑：需根据实际的SEAT_NUMBER格式调整，比如如果座位号是直接以子排为单位（如4ABC），可简化为直接使用SEAT_NUMBER作为sub_row_id。
2. 占用状态匹配：将示例中的'USED'替换为业务中表示座位已占用的availability_attribute实际值（如'OCCUPIED'）。

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