DB2左外连接去重：按规则关联两张表后如何避免重复结果

要解决重复值问题，核心是给每个expenses记录的匹配结果按规则排序，仅保留优先级最高的那条。可以用DB2的ROW_NUMBER()窗口函数实现：

WITH ranked_matches AS (
    SELECT 
        E.ID,
        E.Expense,
        E.JOB AS expense_job,
        ENV.JOB AS env_job,
        ENV.Environment,
        ROW_NUMBER() OVER (
            PARTITION BY E.ID
            ORDER BY 
                -- 优先精确匹配
                CASE WHEN ENV.JOB = E.JOB THEN 1 ELSE 2 END,
                -- 其次取匹配长度最长的（长度差越小，匹配精度越高）
                ABS(LENGTH(ENV.JOB) - LENGTH(E.JOB)),
                -- 多匹配时按环境表Job排序取第一个
                ENV.JOB
        ) AS match_rank
    FROM EXPENSES E
    LEFT JOIN ENVIRONMENTS ENV
        -- 关联条件：覆盖所有前缀/精确匹配场景
        ON LEFT(ENV.JOB, LENGTH(E.JOB)) = E.JOB 
           OR LEFT(E.JOB, LENGTH(ENV.JOB)) = ENV.JOB
)
SELECT 
    ID,
    Expense,
    expense_job AS Job,
    env_job AS Job,
    Environment
FROM ranked_matches
WHERE match_rank = 1
ORDER BY ID;

逻辑说明 #

1. 构建匹配数据集：通过LEFT JOIN关联两张表，关联条件确保所有符合规则的匹配项都被筛选出来——不管是精确匹配，还是某一方的Job前缀与另一方完全一致。
2. 按规则排序：
   • PARTITION BY E.ID：按费用表的每条记录分组，保证每个ID只生成一条最终结果；
   • ORDER BY定义优先级：先取精确匹配的行，再取匹配长度最长的行，最后按环境表Job字段排序取第一个。
3. 筛选最优结果：只保留每个分组中排名第一的记录，即可得到无重复的期望输出。

内容的提问来源于Stack Exchange，提问作者nileger