Oracle 11使用(+)语法实现ca、t表按优先级匹配日期的左连接咨询

问题原因 #

Oracle 传统(+)外连接语法不支持在OR逻辑中同时使用多个外连接字段，你原有的写法会直接触发语法错误或返回不符合预期的结果。

解决方案1：推荐使用ANSI标准LEFT JOIN语法（Oracle 11g全支持） #

该写法逻辑清晰，同时通过排序规则保证匹配优先级，避免出现重复行：

SELECT id, othercolumn, value
FROM (
    SELECT 
        ca.id,
        ca.othercolumn,
        t.value,
        ROW_NUMBER() OVER (
            PARTITION BY ca.id 
            ORDER BY CASE WHEN ca.effective_date = t.effective_date THEN 1 ELSE 2 END
        ) rn
    FROM ca
    LEFT JOIN t ON ca.id = t.id 
        AND (ca.effective_date = t.effective_date OR ca.effective_date = t.ledger_effective_date)
)
WHERE rn = 1;

解决方案2：必须使用传统(+)语法的实现方式 #

拆分为两次左连接，通过NVL函数实现优先级取值，完全符合旧版语法规则：

SELECT 
    ca.id,
    ca.othercolumn,
    NVL(t1.value, t2.value) AS value
FROM ca,
    t t1,
    t t2
WHERE ca.id = t1.id(+)
    AND ca.effective_date = t1.effective_date(+)
    AND ca.id = t2.id(+)
    AND ca.effective_date = t2.ledger_effective_date(+);

验证结果 #

两种写法基于你提供的示例数据，都会返回你需要的期望结果：

id othercolumn  value
1  a            100
2  b            200
3  c            null

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