LEFT JOIN关联两表时，如何获取符合规则的非空ASSET_NUMBER值

问题分析 #

原SQL直接关联PRIMARY_FLAG='Y'的行，存在两个核心问题：

1. 当PRIMARY_FLAG='Y'的行ASSET_NUMBER为空/NULL时，无法自动 fallback 到同ID下其他非空的ASSET_NUMBER
2. 若同一ID存在多个PRIMARY_FLAG='Y'的行，会导致结果行重复相乘

解决方案 #

先对Table2按ID分组，通过窗口函数为每行分配优先级排序，筛选出每个ID符合要求的唯一ASSET_NUMBER，再与Table1左连接。排序规则严格遵循需求：

• 优先选取PRIMARY_FLAG='Y'的行
• 若该行ASSET_NUMBER为空/NULL，则选取同ID下第一个ASSET_NUMBER非空的行

修改后的SQL #

SELECT
    t1.ID,
    t1.NAME,
    t2_processed.ASSET_NUMBER
FROM Table1 t1
LEFT JOIN (
    SELECT
        ID,
        ASSET_NUMBER,
        ROW_NUMBER() OVER (
            PARTITION BY ID
            ORDER BY 
                -- 第一优先级：PRIMARY_FLAG为Y的行
                CASE WHEN PRIMARY_FLAG = 'Y' THEN 1 ELSE 2 END,
                -- 第二优先级：ASSET_NUMBER非空的行
                CASE WHEN ASSET_NUMBER IS NOT NULL AND ASSET_NUMBER != '' THEN 1 ELSE 2 END
        ) AS rn
    FROM Table2
) t2_processed ON t1.ID = t2_processed.ID AND t2_processed.rn = 1;

结果验证 #

执行后将完全匹配预期输出：

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