SQL查询按precedence优先级获取记录的条件编写求助

要实现「优先取precedence=1的记录，无则取precedence=2」的逻辑，这里提供两种实用方案：

方案一：UNION ALL + EXISTS 判断（直观易懂） #

通过UNION ALL拼接两个查询，并用NOT EXISTS确保只有当优先级1无匹配时才返回优先级2的记录：

-- 优先返回precedence=1的匹配记录
SELECT 
    b.precedence, b.template_code, a.CLASS, a.seat_number, 
    int_row_pos, int_column_pos, deck_name, control_attribute
FROM 
    sat_seat_number_tmp a
JOIN sat_ssr_seat b ON 1=1 -- 替换原隐式连接为显式JOIN，可读性更强
WHERE
    a.bd_airport_code = 'GNR'
    AND a.off_airport_code = 'TMP'
    AND a.child_bd_airport_code = 'GNR'
    AND a.child_off_airport_code = 'TMP'
    AND a.CLASS = 'ECONOMY'
    AND a.seat_number MEMBER OF getSeats(v_seatRange => '3A')
    AND b.precedence = 1 
    AND b.SEG_CONNECTION = 'Y'

UNION ALL

-- 仅当precedence=1无匹配时，才返回precedence=2的记录
SELECT 
    b.precedence, b.template_code, a.CLASS, a.seat_number, 
    int_row_pos, int_column_pos, deck_name, control_attribute
FROM 
    sat_seat_number_tmp a
JOIN sat_ssr_seat b ON 1=1
WHERE
    a.bd_airport_code = 'GNR'
    AND a.off_airport_code = 'TMP'
    AND a.child_bd_airport_code = 'GNR'
    AND a.child_off_airport_code = 'TMP'
    AND a.CLASS = 'ECONOMY'
    AND a.seat_number MEMBER OF getSeats(v_seatRange => '3A')
    AND b.precedence = 2 
    AND b.SEG_CONNECTION = 'Y'
    AND NOT EXISTS (
        -- 检查是否存在precedence=1的匹配记录
        SELECT 1 
        FROM sat_seat_number_tmp a1
        JOIN sat_ssr_seat b1 ON 1=1
        WHERE
            a1.bd_airport_code = 'GNR'
            AND a1.off_airport_code = 'TMP'
            AND a1.child_bd_airport_code = 'GNR'
            AND a1.child_off_airport_code = 'TMP'
            AND a1.CLASS = 'ECONOMY'
            AND a1.seat_number MEMBER OF getSeats(v_seatRange => '3A')
            AND b1.precedence = 1 
            AND b1.SEG_CONNECTION = 'Y'
    )

方案二：窗口函数排序（简洁易扩展） #

用ROW_NUMBER()窗口函数按precedence升序排序，直接取优先级最高的第一条记录，后续扩展更多优先级时只需调整筛选条件：

WITH ranked_seats AS (
    SELECT 
        b.precedence, b.template_code, a.CLASS, a.seat_number, 
        int_row_pos, int_column_pos, deck_name, control_attribute,
        -- 按precedence从小到大排序，优先级1排在最前面
        ROW_NUMBER() OVER (ORDER BY b.precedence ASC) AS rn
    FROM 
        sat_seat_number_tmp a
    JOIN sat_ssr_seat b ON 1=1
    WHERE
        a.bd_airport_code = 'GNR'
        AND a.off_airport_code = 'TMP'
        AND a.child_bd_airport_code = 'GNR'
        AND a.child_off_airport_code = 'TMP'
        AND a.CLASS = 'ECONOMY'
        AND a.seat_number MEMBER OF getSeats(v_seatRange => '3A')
        AND b.precedence IN (1,2) -- 限定只查询目标优先级
        AND b.SEG_CONNECTION = 'Y'
)
-- 取排序后的第一条记录（即优先级最高的匹配）
SELECT 
    precedence, template_code, CLASS, seat_number, 
    int_row_pos, int_column_pos, deck_name, control_attribute
FROM ranked_seats
WHERE rn = 1;

方案对比 #

• 方案一逻辑直白，适合数据量较小的场景，无需理解窗口函数即可维护；
• 方案二更简洁，扩展性强，若后续新增优先级（如3、4），只需修改IN (1,2)和排序规则即可。

另外，把原SQL中的隐式表连接改成了显式JOIN，这是SQL的规范写法，能避免歧义，提升代码可读性。

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