无唯一标识符的两张数据表关联获取case_id对应hierarchy_level咨询

问题根源 #

之前关联返回多值、随机匹配的核心原因是：表2中同一个(Family_Number, Family_Description)组合对应多条Hierarchy_Level记录，没有额外匹配规则的情况下，数据库无法判断该返回哪条记录，自然无法得到每个case_id对应的唯一层级。

解决方案 #

需要先明确业务端的层级匹配规则，再通过前置过滤/窗口函数的方式先把表2处理为每个产品族组合对应唯一一条记录，再和表1关联即可：

场景1：明确需要取固定规则的层级 #

如果业务规则是取产品族对应最小/最大层级，或者取GoG_Description和Family_Description前缀匹配的层级，可以用窗口函数对表2分组排序后取首条关联：

-- 先对表2按产品族分组，按规则排序后给每条记录打序号
WITH table2_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Family_Number, Family_Description 
            -- 可按业务需求修改排序规则：
            -- 取最小层级：ORDER BY Hierarchy_Level ASC
            -- 取最大层级：ORDER BY Hierarchy_Level DESC
            -- 取GoG_Description和Family_Description前缀匹配的优先：ORDER BY CASE WHEN Family_Description LIKE CONCAT(GoG_Description, '%') THEN 1 ELSE 2 END ASC
        ) AS rn
    FROM Table2
)
-- 关联时仅取排序后的第一条，保证每个case_id对应唯一层级
SELECT 
    t1.case_id,
    t1.Family_Number,
    t1.Family_Description,
    t2.Hierarchy_Level
FROM Table1 t1
LEFT JOIN table2_ranked t2
    ON t1.Family_Number = t2.Family_Number
    AND t1.Family_Description = t2.Family_Description
    AND t2.rn = 1;

场景2：业务要求每个产品族仅对应一个层级 #

如果规则上同个产品族不能有多个层级，需要先清洗表2的脏数据，先对表2做聚合确认每个产品族的唯一合法层级，再和表1关联：

WITH table2_dedup AS (
    SELECT 
        Family_Number,
        Family_Description,
        -- 按业务要求聚合出唯一层级，比如取众数、取最新录入的等
        MAX(Hierarchy_Level) AS unique_hierarchy_level
    FROM Table2
    GROUP BY Family_Number, Family_Description
)
SELECT 
    t1.case_id,
    t1.Family_Number,
    t1.Family_Description,
    t2.unique_hierarchy_level
FROM Table1 t1
LEFT JOIN table2_dedup t2
    ON t1.Family_Number = t2.Family_Number
    AND t1.Family_Description = t2.Family_Description;

空值说明 #

如果关联后出现Hierarchy_Level为空，是因为表1中的产品族组合（比如示例里的FOAF、FAD6）在表2中没有对应的匹配记录，需要先补全表2的产品族数据，或者在关联时给空值设置默认值。

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