如何在MySQL关联查询中实现迭代式计算？

现有parent与children两张一对多关系的表，需要按特定迭代公式计算父表的最终值：
newParentValue = childMultiple * (parentValue + childSum)，每次迭代用更新后的父值进行下一个子项计算，对应PHP实现逻辑如下。

表结构 #

CREATE TABLE `parent` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`)
);

CREATE TABLE `children` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NOT NULL,
  `multiple` decimal(10,2) DEFAULT NULL,
  `sum` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
);

参考PHP实现 #

function calculateFinalParentValue($parentValue, $children)
{
    foreach ($children as $child) {
        $parentValue = $child['multiple'] * ($parentValue + $child['sum']);
    }
    return $parentValue;
}

尝试的SQL方案（不稳定） #

set @value = 0; 

SELECT 
    p.id,
    @value := (c.multiple * (@value + c.sum)) AS value
FROM
    parent p
JOIN
    children c ON p.id = c.parent_id AND @value := p.value;

该方案依赖关联条件中重置变量的顺序，稳定性差，且需要筛选每个父项的最终计算结果。

示例数据 #

-- parent表数据
+----+-------+
| id | value |
+----+-------+
|  1 | 10.00 |
|  2 | 20.00 |
+----+-------+

-- children表数据
+----+-----------+----------+------+
| id | parent_id | multiple | sum  |
+----+-----------+----------+------+
|  1 |         1 |     1.00 | 1.00 |
|  2 |         1 |     1.00 | 1.00 |
|  3 |         1 |     1.00 | 1.00 |
|  4 |         2 |     2.00 | 2.00 |
|  5 |         2 |     2.00 | 2.00 |
+----+-----------+----------+------+

期望结果 #

• 中间迭代结果（可选查看）：

+----+--------+
| id | value  |
+----+--------+
|  1 |  11.00 |
|  1 |  12.00 |
|  1 |  13.00 | -- parent.id=1的最终值
|  2 |  44.00 |
|  2 |  92.00 | -- parent.id=2的最终值
+----+--------+

• 最终目标结果：

+----+--------+
| id | value  |
+----+--------+
|  1 |  13.00 |
|  2 |  92.00 |
+----+--------+

注：实际场景中同一父项的子表multiple和sum值可能不同，最终值并非必然是最大值。

方法1：使用递归CTE（MySQL 8.0+ 推荐） #

递归CTE可以清晰模拟迭代计算过程，按子项顺序逐步更新父值，最后取每个父项的最后一次迭代结果。

WITH RECURSIVE child_iterations AS (
    -- 初始化：将父表值与第一个子项关联（按子表id排序，确保迭代顺序）
    SELECT 
        p.id AS parent_id,
        p.value AS current_value,
        c.id AS child_id,
        1 AS iteration
    FROM parent p
    JOIN children c ON p.id = c.parent_id
    WHERE c.id = (SELECT MIN(id) FROM children WHERE parent_id = p.id)
    
    UNION ALL
    
    -- 递归迭代：用上一次的结果计算当前子项的新值
    SELECT 
        ci.parent_id,
        c.multiple * (ci.current_value + c.sum) AS current_value,
        c.id AS child_id,
        ci.iteration + 1
    FROM child_iterations ci
    JOIN children c ON ci.parent_id = c.parent_id 
        AND c.id > ci.child_id -- 按子表id顺序处理下一个子项
)
-- 取每个父项的最后一次迭代结果
SELECT 
    parent_id AS id,
    current_value AS value
FROM (
    SELECT 
        parent_id,
        current_value,
        ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY iteration DESC) AS rn
    FROM child_iterations
) t
WHERE rn = 1
ORDER BY parent_id;

方法2：使用自定义变量结合分组（兼容MySQL 5.x） #

如果使用MySQL 5.x版本，可通过变量结合排序和分组来实现，需确保子项按固定顺序处理：

SELECT 
    parent_id AS id,
    MAX(final_value) AS value
FROM (
    SELECT 
        p.id AS parent_id,
        @val := IF(@prev_parent != p.id, p.value, c.multiple * (@val + c.sum)) AS final_value,
        @prev_parent := p.id
    FROM parent p
    JOIN children c ON p.id = c.parent_id
    CROSS JOIN (SELECT @val := 0, @prev_parent := NULL) vars
    ORDER BY p.id, c.id -- 必须按父id和子id排序，保证迭代顺序
) t
GROUP BY parent_id
ORDER BY parent_id;

方法说明 #

• 递归CTE的优势是逻辑清晰，可读性强，便于维护，且不依赖变量的执行顺序，稳定性更高。
• 变量方法需严格保证排序顺序，否则计算结果会出错；适合无法使用CTE的旧版本MySQL。
• 两种方法都依赖子项的处理顺序（示例中按子表id排序），如果业务需要特定顺序（如创建时间），只需调整排序字段即可。

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