Oracle 19c/Toad查询需求：按addr_cd规则展示地址NULL/非NULL值

问题描述 #

我是数据分析新手，正在编写查询从多表提取数据，需求如下：

• 当table A（即table1）中addr_cd不为'R'时，table B的addr1、addr2列显示为NULL；
• 当table A中同一ID同时存在'R'和非'R'的addr_cd时，显示table B的地址实际值。

当前编写的SQL代码如下：

with t1 as 
(
select * from table1 a where a.cd = 'R' and exists
    (select b.cd from table1 b where a.id = b.id and b.cd != 'R')
),
t2 as
(
select * from table1 a where a.cd != 'R' and not exists
    (select b.cd from table1 b where a.id = b.id and b.cd = 'EX')
),
t as 
(
SELECT DISTINCT some column names..,
c.addr1,
c.addr2,
left JOIN t1
        ON c.id = t1.id
        AND c.id2 = t1.id2
        AND A.NBR = t1.NBR 
left JOIN t2    
        ON A.id = t2.id
        AND A.id2 = t2.id2
        AND A.NBR = t2.NBR
) select distinct some columns.., id, addr1, addr2 from t;

目前查询结果出现重复行（一行是addr为NULL的非'R'记录，一行是addr有值的'R'记录），期望输出为：

• 同一ID同时有'R'和非'R'时仅保留地址有值的行；
• ID无'R'值时保留地址为'NULL'的行。

解决方案 #

问题核心是同一ID的多类型记录未做优先级筛选，导致同时保留了'R'和非'R'的结果行。可以通过先标记每个ID是否存在'R'类型记录，再结合条件判断输出地址，避免重复。

优化后SQL代码 #

WITH table1_with_flag AS (
    SELECT 
        *,
        -- 标记当前ID是否存在cd='R'的记录
        MAX(CASE WHEN cd = 'R' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS has_r_flag
    FROM table1
    -- 保留非'EX'的记录（对应原t2的过滤条件）
    WHERE cd != 'EX'
)
SELECT DISTINCT
    -- 替换为你实际需要的字段
    t.id,
    t.id2,
    t.nbr,
    -- 核心逻辑：ID有R记录则取R类型地址，否则输出NULL
    CASE 
        WHEN t.has_r_flag = 1 THEN c.addr1
        ELSE NULL 
    END AS addr1,
    CASE 
        WHEN t.has_r_flag = 1 THEN c.addr2
        ELSE NULL 
    END AS addr2
    -- 其他需要的字段
FROM table1_with_flag t
LEFT JOIN table_b c -- 替换为你的实际地址表名
    ON t.id = c.id
    AND t.id2 = c.id2
    AND t.nbr = c.nbr
-- 有R记录时只保留R类型行，避免重复
WHERE (t.has_r_flag = 0) OR (t.cd = 'R');

逻辑说明 #

1. table1_with_flag通过窗口函数MAX() OVER (PARTITION BY id)，给每个ID标记是否存在'R'类型记录；
2. 关联地址表后，用CASE语句判断：如果ID有'R'记录，就取'R'类型对应的地址；否则输出NULL；
3. 最后通过WHERE条件过滤掉有'R'记录时的非'R'行，确保每个ID只保留一行符合要求的数据，彻底解决重复问题。

如果你的SQL方言不支持窗口函数（如老版本MySQL），可以用子查询先统计每个ID的has_r_flag，再关联查询，逻辑一致。

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