匹配重复时获取单条记录：解决SQL视图关联重复问题

现有表结构及数据 #

主表MASTER #

物品表MASTER_ITEM #

当前问题与需求 #

• 现有创建视图的SQL使用OR关联条件，导致出现重复记录（例如ARTICLE=101的记录）
• 匹配规则要求：优先用物品表的UOM匹配主表的Unit Of Measure（计量单位），匹配成功则返回对应记录；若未匹配到，再匹配主表的Ordering Unit Of Measure（订货计量单位），最终每条物品表记录仅返回一条结果

现有问题SQL #

CREATE OR REPLACE FORCE VIEW "MASTER_ITEM" ("NAME", "BARCODE", "ARTICLE", "UOM", "OUOM") AS 
SELECT m.BARCODE, m.ARTICLE,m.UOM,p.NAME 
FROM MASTER_ITEM m, MASTER p 
WHERE m.BARCODE = p.BARCODE OR (m.ARTICLE = p.ARTICLE AND (p.UOM = m.UOM OR p.OUOM = m.UOM)) 
group BY m.BARCODE,m.ARTICLE, m.UOM,p.NAME;

预期输出结果 #

修正后的SQL解决方案 #

CREATE OR REPLACE FORCE VIEW "MASTER_ITEM_VIEW" ("BARCODE", "ARTICLE", "Unit Of Measure", "Name") AS
WITH ranked_matches AS (
    SELECT
        m.BARCODE,
        m.ARTICLE,
        p."Unit Of Measure" AS "Unit Of Measure",
        p.NAME,
        -- 标记匹配优先级：1=匹配Unit Of Measure，2=匹配Ordering Unit Of Measure
        ROW_NUMBER() OVER (
            PARTITION BY m.BARCODE, m.ARTICLE, m.UOM
            ORDER BY CASE
                WHEN p."Unit Of Measure" = m.UOM THEN 1
                WHEN p."Ordering Unit Of Measure" = m.UOM THEN 2
                ELSE 3
            END
        ) AS match_rank
    FROM MASTER_ITEM m
    LEFT JOIN MASTER p ON 
        m.ARTICLE = p.ARTICLE 
        AND (p."Unit Of Measure" = m.UOM OR p."Ordering Unit Of Measure" = m.UOM)
    WHERE m.BARCODE IS NULL -- 针对无BARCODE的记录处理，如有BARCODE的也需处理可去掉此条件
)
SELECT BARCODE, ARTICLE, "Unit Of Measure", NAME
FROM ranked_matches
WHERE match_rank = 1;

逻辑说明 #

1. 使用ROW_NUMBER()函数按物品表的BARCODE、ARTICLE、UOM分区，给匹配结果按优先级排序
2. 匹配主表Unit Of Measure的记录标记为优先级1，匹配Ordering Unit Of Measure的标记为优先级2
3. 最终只取每个分区中优先级最高（match_rank=1）的记录，确保每条物品表记录仅返回一条结果

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