在MySQL的连接查询中进行计算

fae0ux8s 于 2023-08-02 发布在 Mysql

关注(0)|答案(4)|浏览(82)

我有一些计算，并希望在查询中这样做。
存在一对多关系的父和子表：

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

个字符
为了找到parent的最终值，我应该在children中迭代并计算以下公式：第一个月
代码中的实现如下：

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)中设置变量，以便为每个新的父节点重置变量。
这个查询返回每个父代的行以及子代的数量，我需要每个父代的join中的最后一行作为答案。
但这种方式是不可持续的，有更好的方式吗？
示例如下：

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列都是相等的（假设不同的值），并且最终值是最大值，最终值可能不是每次都是最大值。

mysql

来源：https://stackoverflow.com/questions/76762498/do-calculation-in-join-query-of-mysql

4条答案

按热度按时间

3mpgtkmj1#

有点棘手，因为你必须重置你的值之间的时候，父母得到改变。
请尝试以下查询：

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;

字符串
您也可以将上面提到的CASE语句替换为IF（可读性更强一点）

IF(@current_parent_value=0, @current_parent_value:=parentValue, @current_parent_value=@current_parent_value),
IF(@running_parent!=parentId, @current_parent_value:=parentValue, @current_parent_value:=@current_parent_value),

型
它应该给予你你想要的输出。
我使用了两个变量@current_parent_value和@running_parent
@running_parent将帮助您确定前一行和当前行是否属于相同的parent，@current_parent_value将帮助您存储当前的运行值。

赞(0）回复(0）举报 2023-08-02

ao218c7q2#

使用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;

字符串
参见demo。

赞(0）回复(0）举报 2023-08-02

lmvvr0a83#

为了获得预期的输出，我们需要首先识别每个父表的最后一个子表，然后将其与父表和子表连接：

SELECT p.id, c.multiple * ( p.value + c.sum )
FROM parent p
INNER JOIN (
  select parent_id, max(id) as last_row
  from children
  group by parent_id
) as s on s.parent_id = p.id
inner join children c on c.id = s.last_row

字符串

赞(0）回复(0）举报 2023-08-02

lbsnaicq4#

在SQL中，这看起来像：

SELECT
    `parent`.`id`,
    `children`.`multiple` * ( `parent`.`value` + `children`.`sum` ) as CalculatedValue
FROM `parent`
INNER JOIN `children` ON `parent`.`id` = `children`.`parent_id`

字符串

赞(0）回复(0）举报 2023-08-02

我来回答

在MySQL的连接查询中进行计算

4条答案

相关问题

热门标签

最新问答