实现带条件的Left outer join 关联台账与部门参考表匹配BMDIV

实现思路 #

需求核心是匹配规则带优先级：优先匹配LCTRYNUM、LC、DEPTNUM三个字段完全相等的业务部门参考表记录，无匹配时再匹配参考表LC为'**'的LCTRYNUM、DEPTNUM两字段关联记录。

方案1：双左连接实现（推荐，逻辑简单性能好） #

SELECT 
    l.LCTRYNUM,
    l.LC,
    l.DEPTNUM,
    l.Amount,
    -- 优先取精确匹配的BMDIV，无匹配取通配规则的BMDIV
    COALESCE(d1.BMDIV, d2.BMDIV) AS BMDIV
FROM ledger l
-- 第一次关联：精确匹配三个字段
LEFT JOIN dept_ref d1 
    ON l.LCTRYNUM = d1.LCTRYNUM 
    AND l.LC = d1.LC 
    AND l.DEPTNUM = d1.DEPTNUM
-- 第二次关联：匹配LC为通配符**的两字段规则
LEFT JOIN dept_ref d2 
    ON l.LCTRYNUM = d2.LCTRYNUM 
    AND l.DEPTNUM = d2.DEPTNUM 
    AND d2.LC = '**'

说明：上述SQL中ledger为台账实际表表名，dept_ref为业务部门参考表表名，可根据实际表名替换。

方案2：单关联加窗口函数实现（适合复杂多优先级场景） #

WITH matched_data AS (
    SELECT 
        l.LCTRYNUM,
        l.LC,
        l.DEPTNUM,
        l.Amount,
        d.BMDIV,
        -- 优先级排序：精确匹配排序在前，通配匹配在后
        ROW_NUMBER() OVER (
            PARTITION BY l.LCTRYNUM, l.LC, l.DEPTNUM, l.Amount 
            ORDER BY CASE WHEN d.LC = '**' THEN 2 ELSE 1 END
        ) AS rn
    FROM ledger l
    LEFT JOIN dept_ref d
        ON l.LCTRYNUM = d.LCTRYNUM
        AND l.DEPTNUM = d.DEPTNUM
        AND (d.LC = l.LC OR d.LC = '**')
)
SELECT LCTRYNUM, LC, DEPTNUM, Amount, BMDIV
FROM matched_data
WHERE rn = 1

两种方案均可输出你提供的预期结果。

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