我有一些计算，并想在查询中执行此操作。
存在具有一对多关系的父和子表：
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`)
);
为了找到父级的最终值，我应该迭代子级并计算以下公式：
newParentValue = childMultiple(parentValue + childSum)
代码中的实现如下：
function calculateFinalParentValue($parentValue, $children)
{
foreach ($children as $child) {
$parentValue = $child['multiple'] * ($parentValue + $child['sum']);
}
return $parentValue;
}
如何在查询中实现计算？
我尝试这种方式（使用临时变量）：
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;
我在连接条件 (@value := p.value) 中设置变量以重置每个新父级的变量。
此查询返回每个父级的行以及子级的数量，我需要每个父级连接中的最后一行作为答案。
但是这种方式不可持续，有更好的方式吗？
示例：
mysql> select * from parent; +----+-------+ | id | value | +----+-------+ | 1 | 10.00 | | 2 | 20.00 | +----+-------+ mysql> select * from 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 | <- final value for parant.id = 1 | 2 | 44.00 | | 2 | 92.00 | <- final value for parant.id = 2 +----+--------+
对于parent.id=1，有三个孩子，parent.value为10，因此在计算第一个孩子的公式后，新值是 1 * (10 + 1) = 11 ，第二个孩子的值是 1 * (11 + 1) = 12 正如预期的那样，第三个子值是 1 * (12 + 1) = 13 （在所有三个子项中都是倍数，总和等于 1）。
对于parent.id=2，有两个孩子，parent.value为20，因此在计算第一个孩子的公式后，新值是 2 * (20 + 2) = 44 ，第二个孩子的值是 2 * (44 + 2) = 92 （两个孩子都是倍数且总和等于 2）。
最后我只想要每个父母的最终值，所以我的最终预期结果是：
+----+--------+ | id | value | +----+--------+ | 1 | 13.00 | | 2 | 92.00 | +----+--------+
只是为了简化示例，每个父级的子表的所有 multiply 和 sum 列都是相等的（假设不同的值）并且最终值为最大值，最终值可能不是每次都是最大值。< /p>
使用
ROW_NUMBER()窗口函数对children的行进行排名，按parent_id分区并按id和SUM()窗口函数进行排序，以获得所需的总和。最后使用
FIRST_VALUE()窗口函数获取每个id的最后一个总和：WITH cte_children AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) rn FROM children), cte_sums AS ( SELECT p.id, c.rn, POW(c.multiple, c.rn) * p.value + SUM(POW(c.multiple, c.rn)) OVER (PARTITION BY p.id ORDER BY c.rn) * c.sum value FROM parent p INNER JOIN cte_children c ON c.parent_id = p.id ) SELECT DISTINCT id, FIRST_VALUE(value) OVER (PARTITION BY id ORDER BY rn DESC) value FROM cte_sums;查看演示。
有一点棘手，因为在父级发生变化时，你必须在中间重置你的值。
尝试以下查询：
SELECT parentId, ROUND(iteratingValue, 2) reqValue FROM (SELECT parentId, `childMultiple`, childSum, @running_parent, (CASE WHEN @current_parent_value=0 THEN @current_parent_value:=parentValue ELSE @current_parent_value=@current_parent_value END) , (CASE WHEN @running_parent!=parentId THEN @current_parent_value:=parentValue ELSE @current_parent_value:=@current_parent_value END), @current_parent_value:=(`childMultiple`*(@current_parent_value+childSum)) AS iteratingValue, @running_parent:=parentId FROM (SELECT p.`id` parentId, c.`multiple`childMultiple, p.`value` parentValue, c.`sum` AS childSum, @current_parent_value:=0, @running_parent:=0 FROM parent p JOIN `children` c ON c.`parent_id`=p.`id` ) subTable ORDER BY parentId) finalTable;你也可以用
IF语句替换上述提到的CASE语句（更易读一些）这应该给你想要的输出。
我使用了两个变量
@current_parent_value和@running_parent@running_parent将帮助你确定前一行和当前行是否属于同一个parent，而@current_parent_value将帮助你存储当前的运行值。