从节点树获取总和 [英] 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?我现在需要使用递归，但是我尝试了并且没有任何反应. 选择，更新和其他命令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.

您的表可能如下所示:

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的钱呢?

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.

另一种获取层次结构数据的流行方法是使用修改的预序树遍历".多年来，站点点上有一篇非常好的文章，它解释了这是怎么回事.完成了大量示例代码.

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屋！