基于ctr_id关联填充NULL金额的SQL需求及尝试方案

需求说明 #

要将ctr_id为2537的行的金额显示为6369，核心规则是：当某行的ctr_amount_consumed为NULL时，找到其ctr_id_parent对应的ctr_id行的ctr_amount_consumed值来填充。

示例输入数据 #

预期结果 #

你的代码问题分析 #

你尝试的CTE写法存在几个关键问题：

• 两个CTE a和b都只筛选了ctr_amount_consumed非NULL的行，漏掉了需要填充金额的NULL行
• 使用CROSS JOIN会生成所有行的笛卡尔积，完全没有按照父ID关联的逻辑，和需求不符
• 注释掉了关联条件ON a.ctr_id = b.ctr_id_parent，进一步导致逻辑错误

正确的SQL写法 #

方案1：单层父级填充（适用于当前示例场景）

使用自连接结合COALESCE函数，优先取当前行的金额，若为NULL则取父行的金额：

SELECT 
    t1.ctr_id,
    t1.ctr_id_parent,
    COALESCE(t1.ctr_amount_consumed, t2.ctr_amount_consumed) AS amount
FROM fk1 t1
LEFT JOIN fk1 t2 
    ON t1.ctr_id_parent = t2.ctr_id;

方案2：递归CTE（适用于多层级父级都可能为NULL的场景）

如果存在多层级嵌套（比如父行的金额也为NULL，需要继续向上找），可以用递归CTE实现：

WITH RECURSIVE cte AS (
    -- 初始行：所有数据
    SELECT 
        ctr_id,
        ctr_id_parent,
        ctr_amount_consumed AS amount
    FROM fk1
    UNION ALL
    -- 递归关联父行，直到找到非NULL的金额
    SELECT 
        c.ctr_id,
        c.ctr_id_parent,
        p.amount
    FROM cte c
    JOIN fk1 p 
        ON c.ctr_id_parent = p.ctr_id
    WHERE c.amount IS NULL
)
-- 取每个ctr_id最后找到的非NULL金额
SELECT 
    ctr_id,
    ctr_id_parent,
    MAX(amount) AS amount
FROM cte
GROUP BY ctr_id, ctr_id_parent;

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