如何使用MySQL递归查询补全存在空Price的产品费率价格表？

你提到的场景确实适合用**递归CTE（Common Table Expression）**来处理，因为费率映射是链式的，需要沿着映射关系一直找到有有效价格的费率。MySQL 8.0及以上版本支持递归CTE，刚好能满足这个需求。

先明确我们的目标：对每个id_product+rate组合，如果Price为NULL，就沿着费率映射表的Origin_rate→Destination_rate链条，找到第一个该产品下有非NULL Price的费率，用它的价格补全。

完整解决方案代码 #

WITH RECURSIVE rate_price_chain AS (
    -- 锚点成员：先获取所有有有效价格的记录，作为递归的起点
    SELECT 
        id_product,
        rate AS current_rate,
        rate AS original_rate,
        Price
    FROM 产品费率价格表
    WHERE Price IS NOT NULL

    UNION ALL

    -- 递归成员：处理价格为空的记录，沿着映射表找下一个费率
    SELECT 
        p.id_product,
        m.Destination_rate AS current_rate,
        p.rate AS original_rate,
        rpc.Price
    FROM 产品费率价格表 p
    JOIN 费率映射表 m ON p.rate = m.Origin_rate
    JOIN rate_price_chain rpc 
        ON rpc.id_product = p.id_product 
        AND rpc.current_rate = m.Destination_rate
    WHERE p.Price IS NULL
)
-- 最终查询：每个原始rate只保留最顶层的结果
SELECT 
    id_product,
    original_rate AS rate,
    Price
FROM rate_price_chain
WHERE original_rate = current_rate
ORDER BY id_product, rate;

代码解释 #

1. 锚点成员：

   • 先把所有Price不为NULL的记录拉出来，这些是我们的"基础有效数据"，记录里的current_rate和original_rate都是自身，因为不需要映射。

2. 递归成员：

   • 针对Price为NULL的产品费率记录，通过费率映射表找到它的替代费率Destination_rate。
   • 然后关联递归CTE的结果，找到该替代费率对应的有效价格（题目说最终总能找到，所以递归会终止）。
   • 这里original_rate始终保留最初的那个费率（比如TSA3），current_rate则是当前递归到的费率（比如TASM4）。

3. 最终查询：

   • 筛选出original_rate = current_rate的记录，也就是每个原始费率对应的最终有效价格（要么是自身的非NULL价格，要么是递归找到的映射后的价格）。

测试你的示例数据 #

用你给出的数据运行这段代码，得到的结果会是：

完全符合你的需求：产品1的TSA3价格被补全为TASM4的1.68，其他记录保持原样。

注意事项 #

• MySQL版本要求：必须是MySQL 8.0或更高版本，因为之前的版本不支持递归CTE。
• 映射链终止：题目说明映射关系最终总能找到有效Price，所以不用担心递归无限循环。如果有循环风险，可以在递归成员里加一个深度限制（比如加一个depth字段，当depth > 10时停止）。
• 性能考虑：如果数据量很大，建议给产品费率价格表的id_product+rate、费率映射表的Origin_rate加上索引，提升递归查询的效率。

内容的提问来源于stack exchange，提问作者François Maurice