如何移除Oracle查询中的WITH子句且保证结果一致？

你的思路方向是对的——确实可以将WITH中的逻辑整合到JOIN环节，不需要用到HAVING子句。原WITH里的两个子句本质是筛选出每个adr_id对应的最新（eff_ts最大）地址记录，下面直接给出两种等价的改写方案，保证结果一致：

方案一：将WITH逻辑合并为关联子查询 #

直接把QA_m的逻辑作为子查询，和ADDR表关联，替代原有的QA_address：

select
    c.SRGT_KEY_VAL as Customer_id, 
    CAST(i.EFF_TS as date) as "EFF_DT",
    i.EFF_TS,
    '9999-12-31' as END_DT,
    'N' as DEL_IND,
    'I' as CUSTOMER_TYPE,
    case when ctr.NAME = 'Canada' then 'Y'
             else 'N' 
                 END as RESIDENCE_FLAG,
    NVL(ctr.name,'N/A') as country,
    NVL((trim(TITLE) ||' '||trim(FIRST_NAME)||' '||trim(LAST_NAME)),' ') as NAME,
    NVL((trim(b.CITY)||' '||trim(b.POST_CODE)||' '||trim(b.STREET)||' '||trim(b.UNIT_NBR)||' '||trim(b.ADL_INFO)),' ') as ADDRESS,
    case when i.BIRTH_DATE=TO_date('9999-12-31') then NULL
             else TRUNC(months_between(sysdate, i.BIRTH_DATE) / 12) 
                 end AGE,
    case when substr(GENDER,1,1) = 'M' then 'M'
         when substr(GENDER,1,1) = 'm' then 'M'
         when substr(GENDER,1,1) = 'F' then 'F'
         when substr(GENDER,1,1) = 'f' then 'F'
             else NULL 
                 end as GENDER,
    NULL as VAT_NUMBER,
    NULL as BRANCH,
    NULL as EMPLOYEES                                       
from IDV i 
    join CSTMR_SRGT_KEY c
        on i.IDV_ID=c.ntrl_key_val 
    left join (
        select d.adr_id, d.COUNTRY_ID, d.city, d.POST_CODE, d.STREET, d.UNIT_NBR, d.ADL_INFO
        from ADDR d
        join (
            select adr_id, max(eff_ts) as EFF_TS 
            from addr group by adr_id
        ) q on d.adr_id = q.adr_id and d.EFF_TS = q.EFF_TS
    ) b on i.adr_ID = b.adr_id 
   left join COUNTRY ctr
        on ctr.COUNTRY_ID = b.COUNTRY_ID
where SRC_STM_ID = 100
    and i.END_TS='9999-12-31 23:59:59.999999000'  
    and i.DEL_IND='N';

方案二：用窗口函数简化逻辑（更高效） #

利用Oracle的ROW_NUMBER()窗口函数，直接在ADDR表中筛选出每个adr_id的最新记录，避免嵌套子查询：

select
    c.SRGT_KEY_VAL as Customer_id, 
    CAST(i.EFF_TS as date) as "EFF_DT",
    i.EFF_TS,
    '9999-12-31' as END_DT,
    'N' as DEL_IND,
    'I' as CUSTOMER_TYPE,
    case when ctr.NAME = 'Canada' then 'Y'
             else 'N' 
                 END as RESIDENCE_FLAG,
    NVL(ctr.name,'N/A') as country,
    NVL((trim(TITLE) ||' '||trim(FIRST_NAME)||' '||trim(LAST_NAME)),' ') as NAME,
    NVL((trim(b.CITY)||' '||trim(b.POST_CODE)||' '||trim(b.STREET)||' '||trim(b.UNIT_NBR)||' '||trim(b.ADL_INFO)),' ') as ADDRESS,
    case when i.BIRTH_DATE=TO_date('9999-12-31') then NULL
             else TRUNC(months_between(sysdate, i.BIRTH_DATE) / 12) 
                 end AGE,
    case when substr(GENDER,1,1) = 'M' then 'M'
         when substr(GENDER,1,1) = 'm' then 'M'
         when substr(GENDER,1,1) = 'F' then 'F'
         when substr(GENDER,1,1) = 'f' then 'F'
             else NULL 
                 end as GENDER,
    NULL as VAT_NUMBER,
    NULL as BRANCH,
    NULL as EMPLOYEES                                       
from IDV i 
    join CSTMR_SRGT_KEY c
        on i.IDV_ID=c.ntrl_key_val 
    left join (
        select adr_id, COUNTRY_ID, city, POST_CODE, STREET, UNIT_NBR, ADL_INFO
        from (
            select 
                d.*,
                ROW_NUMBER() over (partition by adr_id order by eff_ts desc) as rn
            from ADDR d
        )
        where rn = 1
    ) b on i.adr_ID = b.adr_id 
   left join COUNTRY ctr
        on ctr.COUNTRY_ID = b.COUNTRY_ID
where SRC_STM_ID = 100
    and i.END_TS='9999-12-31 23:59:59.999999000'  
    and i.DEL_IND='N';

说明 #

• 两种方案都完全等价于原查询的结果，没有使用WITH子句
• 方案二的窗口函数写法在数据量较大时性能更优，因为只需要扫描ADDR表一次
• 原思路中提到的HAVING子句并不适用这里，因为我们需要的是关联获取对应记录，而不是分组后的过滤条件

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