Oracle SQL实现多表关联优先取CODE='10'记录的查询方案

现有3张Oracle数据库表结构如下：

• TABLE1：仅包含非空字段ROLL_NO NUMBER(3)
• TABLE2：包含3个非空字段：ROLL_NO NUMBER(3)、CLASS NUMBER(3)、SEC NUMBER(3)
• TABLE3：包含3个非空字段：ROLL_NO NUMBER(3)、CODE VARCHAR2(3)、AMT NUMBER(3)

现有查询逻辑为TABLE1内连接TABLE2、左连接TABLE3查询指定ROLL_NO集合的记录，对TABLE3无匹配的记录通过NVL函数将CODE、AMT字段默认赋值为0，该查询会返回同一ROLL_NO关联的多条TABLE3记录。

需要按以下规则筛选得到目标结果：

• 若某ROLL_NO同时存在CODE='10'和其他CODE值的记录，结果中仅保留CODE='10'的对应行
• 若某ROLL_NO不存在CODE='10'的记录，则保留该ROLL_NO对应的所有其他CODE记录行

此前尝试使用RANK()窗口函数，按ROLL_NO分区、CODE字段降序排序后取每个分区首行实现需求，但该方案在ROLL_NO存在大于'10'的CODE值（如'11'/'20'）时会错误返回大值CODE的行，无法满足规则要求。

核心逻辑是优先判断每个ROLL_NO下是否存在CODE='10'的记录，再根据判断结果做分支过滤，完全规避按CODE字符串排序导致的优先级错误。

方案1：窗口函数实现（推荐，性能更优） #

仅需对原有查询结果做一次窗口函数计算即可完成标记，无需重复访问表：

WITH base_data AS (
    -- 此处保留原有查询逻辑无需修改
    SELECT
        t1.ROLL_NO,
        t2.CLASS,
        t2.SEC,
        NVL(t3.CODE, '0') AS CODE,
        NVL(t3.AMT, 0) AS AMT
    FROM TABLE1 t1
    INNER JOIN TABLE2 t2
        ON t1.ROLL_NO = t2.ROLL_NO
    LEFT JOIN TABLE3 t3
        ON t1.ROLL_NO = t3.ROLL_NO
    -- 可在此处添加原有ROLL_NO集合过滤条件，例如 WHERE t1.ROLL_NO IN (1,2,3)
),
marked_data AS (
    SELECT
        b.*,
        -- 为同ROLL_NO下所有行打标记：存在CODE='10'则标记为1，否则为0
        MAX(CASE WHEN b.CODE = '10' THEN 1 ELSE 0 END) OVER (PARTITION BY b.ROLL_NO) AS has_code_10
    FROM base_data b
)
SELECT ROLL_NO, CLASS, SEC, CODE, AMT
FROM marked_data
WHERE
    (has_code_10 = 1 AND CODE = '10')
    OR has_code_10 = 0;

方案2：EXISTS逻辑实现 #

无需CTE，直接在WHERE子句中做存在性判断，写法更直观：

SELECT
    t1.ROLL_NO,
    t2.CLASS,
    t2.SEC,
    NVL(t3.CODE, '0') AS CODE,
    NVL(t3.AMT, 0) AS AMT
FROM TABLE1 t1
INNER JOIN TABLE2 t2
    ON t1.ROLL_NO = t2.ROLL_NO
LEFT JOIN TABLE3 t3
    ON t1.ROLL_NO = t3.ROLL_NO
WHERE
    -- 当前行是CODE='10'的记录直接保留
    NVL(t3.CODE, '0') = '10'
    -- 若当前ROLL_NO下不存在任何CODE='10'的记录，所有行都保留
    OR NOT EXISTS (
        SELECT 1
        FROM TABLE3 t3_check
        WHERE t3_check.ROLL_NO = t1.ROLL_NO
          AND t3_check.CODE = '10'
    );

• CODE字段为VARCHAR2类型，判断值时需使用字符串格式'10'，不要直接写数字10，避免隐式类型转换引发索引失效、匹配错误问题
• 左连接空值通过NVL赋值为'0'的逻辑不受影响，无TABLE3匹配的记录会在对应ROLL_NO无CODE='10'时正常返回

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