MySQL递归填充：基于父级补全子级空折扣值

需求说明 #

我通过关联两张表得到了一个递归结构的表，表中ID与ParentID构成层级关系。需要新增CalculatedDiscount列，规则如下：

• 若当前分组的折扣值不为空，CalculatedDiscount直接取该值
• 若当前分组的折扣值为空，向上追溯父级分组，取第一个非空的折扣值填充；若父级也为空，则继续向上直到根分组

解决方案 #

利用递归CTE（公共表表达式）处理层级结构，在递归过程中携带并传递最近的有效折扣值：

WITH RecursiveDiscount AS (
    -- 初始化：处理根节点（无父节点的记录）
    SELECT 
        ID,
        ParentID,
        Discount,
        Discount AS CalculatedDiscount
    FROM YourRecursiveTable
    WHERE ParentID IS NULL OR ParentID NOT IN (SELECT ID FROM YourRecursiveTable)

    UNION ALL

    -- 递归遍历子节点
    SELECT 
        child.ID,
        child.ParentID,
        child.Discount,
        -- 优先用自身折扣，为空则继承父节点的已计算折扣
        COALESCE(child.Discount, parent.CalculatedDiscount) AS CalculatedDiscount
    FROM YourRecursiveTable child
    INNER JOIN RecursiveDiscount parent 
        ON child.ParentID = parent.ID
)
SELECT * FROM RecursiveDiscount ORDER BY ID;

代码解释 #

1. 根节点处理：首先定位所有层级的根节点（ParentID为空或不存在对应父节点ID的记录），这些节点的CalculatedDiscount直接使用自身的Discount值。
2. 递归子节点：遍历每个子节点时，使用COALESCE函数判断：如果子节点自身有折扣值则直接使用，否则继承父节点已经计算好的CalculatedDiscount——这个值已经是父级向上追溯到的第一个非空折扣，以此实现层级间的空值补全。
3. 若根节点的Discount也为空，该分支下所有节点的CalculatedDiscount会保持NULL，因为没有更上层的节点可以追溯。

内容的提问来源于stack exchange，提问作者jan-peter Poppe