从节点树中获取总和 [英] Get sum from nodes tree

问题描述

我正在学习 php.我有这个结构

I'm learn php. I have this struct

company 1 - $10| all $50
-company 1.1 - $10| all $20
--company 1.1.1 - 10$| all $10
-company 1.2 - $20| all $20

每个公司可能有多个子公司，并且可能只有一个母公司.每个公司都有钱.所有公司都有 Allmoney - 自己的钱 + 他所有子公司的钱.

each company might have several child-company, and might only one parent. Each company has money. All companys have Allmoney - own money + money of all his child companys.

在 MySQL 中这个结构是这样的

In MySQL this struct like this

id|parent_id|name|money|allmoney
1| 0| company 1| 10|###
2| 1| company 1.1|10 |###
3| 2| company 1.1.1|10 |###
4| 1| company 1.2|10 |###

那么，我如何在 php 中计算每家公司的 allmoney?我现在，需要使用递归，但我尝试并没有什么不可能发生.SELECT、UPDATE 和其他命令 mysql - 我知道，请帮助我使用 php.我写的东西是这样的:

so, How I calculate allmoney for each company in php? I now, that need using recursion, but I try and nothing can't happen. SELECT, UPDATE and other command mysql - I know, Please help me with php. I writing something like this:

function updatemoney($id)
    {
        $data = CS50::query("SELECT ...", $id);
        $allmoney = 0;

        if(count($data) > 0)
        {
            foreach($data as $row)
            {

                $allmoney += $row["cash"];
               //somewhere this, maybe need ubdate my db 
                $allmoney += updatemoney($row["id"]);
            }
        }
        else return 0;
    }

非常感谢

推荐答案

LTREE

您几乎走在正确的轨道上.您几乎偶然发现了在数据库中存储分层数据的LTREE"系统.您只需要稍作修改即可.仅此而已.

LTREE

You are almost on the right track. You almost stumbled upon the 'LTREE' system of storing hierachial data in a database. You just need to make a slight modidification. that's all.

您的表格可能如下所示:

Your table might look like this:

CREATE TABLE Table1
    (`id` int, `parent_id` int, `name` varchar(13),
     `path` char(10),
     `money` int)
;

您的数据可能如下所示.

And your data might look like this.

(1, 0, 'company 1', '1', 10),
(2, 1, 'child 1', '1.1', 10),
(3, 2, 'child 2', '1.1.1', 10),
(4, 1, 'child 3', '1.2', 10,),
(4, 1, 'company 2', '2', 10),
(4, 1, 'child 2.1', '2.1', 10)

路径列有助于识别哪家公司是另一家公司的子公司.请注意，您实际上并不需要 allmoney 列.这是动态生成的.

The path column helps to identify which company is a subsidiary of another company. Notice that you don't actually need to have an allmoney column. This is dynamically generated.

你如何找到属于第一家公司的所有资金?

And how do you find all the money that belongs to the first company?

select sum(money) from Table1 where path >= '1' and path < '2'

请注意，在我们创建的结构中，child1 是 child2 的父级.那么我们如何找到 child1 的 allmoney?

Notice that in the structure that we have created, child1 is the parent for child2. So how do we find the allmoney for child1?

select sum(money) from Table1 where path >= '1.1' and path < '1.2'

只有一个查询，没有递归.

There is only one query and no recursion.

另一种获取分层数据的流行方法是使用修改后的预排序树遍历.多年来，有一篇关于 Sitepoint 的优秀文章解释了这是怎么回事使用大量示例代码完成.

Another popular approach for fetching hierarchical data is using Modified Pre Order Tree Traversal. There has been an excellent article on Sitepoint for many years which explains how this is done with lot's of sample code.

这篇关于从节点树中获取总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！