基于优先级从另一表更新NULL值的Teradata SQL查询需求

前提假设 #

假设表结构如下：

• Table1：包含ID（主键）、Store1、Store2、Store3，其中部分字段为NULL需要填充
• Table2：包含ID、Store（待填充的店铺名称）、Priority（优先级字段，数字越小优先级越高，用于确定填充顺序）

如果你的Table2没有Priority字段，可以将排序逻辑替换为ORDER BY Store或其他业务相关字段。

完整SQL查询 #

WITH ranked_stores AS (
    -- 对每个ID的店铺去重，并按优先级排序生成序号
    SELECT 
        ID,
        Store,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Priority) AS rn
    FROM (
        SELECT DISTINCT ID, Store, Priority
        FROM Table2
    ) deduplicated_stores
),
store_pivoted AS (
    -- 将排序后的店铺转成对应位置的填充值，对应第1/2/3优先级的店铺
    SELECT
        ID,
        MAX(CASE WHEN rn = 1 THEN Store END) AS fill_store1,
        MAX(CASE WHEN rn = 2 THEN Store END) AS fill_store2,
        MAX(CASE WHEN rn = 3 THEN Store END) AS fill_store3
    FROM ranked_stores
    GROUP BY ID
),
final_result AS (
    SELECT
        t1.ID,
        -- 填充Store1：优先保留原值，为空则取最高优先级填充值
        COALESCE(t1.Store1, sp.fill_store1) AS Store1,
        -- 填充Store2：优先保留有效原值（非空且与Store1不重复），否则取第一个未被Store1使用的高优先级填充值
        CASE
            WHEN t1.Store2 IS NOT NULL AND t1.Store2 != COALESCE(t1.Store1, sp.fill_store1) THEN t1.Store2
            ELSE CASE
                WHEN sp.fill_store1 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store1
                WHEN sp.fill_store2 IS NOT NULL AND sp.fill_store2 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store2
                ELSE sp.fill_store3
            END
        END AS Store2,
        -- 填充Store3：优先保留有效原值（非空且与Store1、Store2都不重复），否则取第一个未被前两个使用的填充值
        CASE
            WHEN t1.Store3 IS NOT NULL 
                 AND t1.Store3 != COALESCE(t1.Store1, sp.fill_store1) 
                 AND t1.Store3 != CASE
                                     WHEN t1.Store2 IS NOT NULL AND t1.Store2 != COALESCE(t1.Store1, sp.fill_store1) THEN t1.Store2
                                     ELSE CASE
                                         WHEN sp.fill_store1 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store1
                                         WHEN sp.fill_store2 IS NOT NULL AND sp.fill_store2 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store2
                                         ELSE sp.fill_store3
                                     END
                                 END 
            THEN t1.Store3
            ELSE CASE
                WHEN sp.fill_store1 != COALESCE(t1.Store1, sp.fill_store1) 
                     AND sp.fill_store1 != CASE
                                             WHEN t1.Store2 IS NOT NULL AND t1.Store2 != COALESCE(t1.Store1, sp.fill_store1) THEN t1.Store2
                                             ELSE CASE
                                                 WHEN sp.fill_store1 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store1
                                                 WHEN sp.fill_store2 IS NOT NULL AND sp.fill_store2 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store2
                                                 ELSE sp.fill_store3
                                             END
                                         END 
                THEN sp.fill_store1
                WHEN sp.fill_store2 IS NOT NULL 
                     AND sp.fill_store2 != COALESCE(t1.Store1, sp.fill_store1) 
                     AND sp.fill_store2 != CASE
                                             WHEN t1.Store2 IS NOT NULL AND t1.Store2 != COALESCE(t1.Store1, sp.fill_store1) THEN t1.Store2
                                             ELSE CASE
                                                 WHEN sp.fill_store1 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store1
                                                 WHEN sp.fill_store2 IS NOT NULL AND sp.fill_store2 != COALESCE(t1.Store1, sp.fill_store1) THEN sp.fill_store2
                                                 ELSE sp.fill_store3
                                             END
                                         END 
                THEN sp.fill_store2
                ELSE sp.fill_store3
            END
        END AS Store3
    FROM Table1 t1
    LEFT JOIN store_pivoted sp 
        ON t1.ID = sp.ID
)
SELECT * FROM final_result;

逻辑说明 #

1. ranked_stores CTE：先对Table2按ID去重，再用ROW_NUMBER()按优先级给每个ID下的店铺排序，生成1、2、3的序号，对应填充优先级。
2. store_pivoted CTE：将排序后的店铺转成列，得到每个ID对应的3个优先级填充值，方便后续关联填充。
3. final_result CTE：
   • 对Store1直接用COALESCE保留原值或取最高优先级填充值
   • 对Store2先判断原值是否有效（非空且不与Store1重复），无效则从填充值中选第一个未被Store1使用的高优先级值
   • 对Store3同理，先验证原值有效性，无效则选未被前两个Store使用的填充值

调整说明 #

• 如果Table2没有Priority字段，将ORDER BY Priority替换为ORDER BY Store或其他业务排序字段即可。
• 如果需要支持更多Store字段，可扩展ranked_stores和store_pivoted中的序号和对应列。

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