关于树形结构求和的问题

[cexbzxh]

概算表 t_Budget 结构如下：
budget_id 概算ID（主键）
budget_name 概算名称
budget_level 概算级别（根目录为0）
parent_id 父节点ID
child_num 子节点数量（叶子节点为0）
avail_amount 可用金额 （仅叶子节点有数值）
可用金额（avail_amount ）由人工输入
现在想以树形结构显示概算列表，同时根据叶子节点计算各父节点的金额，请问该如何编写SQL语句，谢谢大家！

[shoppo0505]

cte递归 可解决问题

[Net攻城狮]

你可以学习下数据库递归

[中国风]

计算人手添加的金额 e.g.

;WITH CTET 
AS
(
SELECT budget_id,parent_id,avail_amount,ID=budget_id FROM t_Budget
UNION ALL
SELECT a.budget_id,a.parent_id,a.avail_amount,b.ID FROM t_Budget AS a INNER JOIN CTET AS b ON b.parent_id=a.budget_id
)

SELECT  a.* ,
        b.avail_amount AS [各节点总金额]
FROM    t_Budget AS a
        INNER JOIN ( SELECT ID ,
                            SUM(avail_amount) AS avail_amount
                     FROM   CTET
                     GROUP BY ID
                   ) AS b ON a.budget_id = b.ID

[cexbzxh]

引用 3 楼 roy_88 的回复:

计算人手添加的金额 e.g.

;WITH CTET 
AS
(
SELECT budget_id,parent_id,avail_amount,ID=budget_id FROM t_Budget
UNION ALL
SELECT a.budget_id,a.parent_id,a.avail_amount,b.ID FROM t_Budget AS a INNER JOIN CTET AS b ON b.parent_id=a.budget_id
)

SELECT  a.* ,
        b.avail_amount AS [各节点总金额]
FROM    t_Budget AS a
        INNER JOIN ( SELECT ID ,
                            SUM(avail_amount) AS avail_amount
                     FROM   CTET
                     GROUP BY ID
                   ) AS b ON a.budget_id = b.ID

试了一下，最初非叶子节点均为0 ，运行后的各节点总金额字段，非叶子节点依然是0，我就是想得到非叶子节点的金额。

[中国风]

引用 4 楼 cexbzxh 的回复:

试了一下，最初非叶子节点均为0 ，运行后的各节点总金额字段，非叶子节点依然是0，我就是想得到非叶子节点的金额。

条件反过来关联 查所有 e.g.

;WITH CTET 
AS
(
SELECT budget_id,parent_id,avail_amount,ID=budget_id FROM t_Budget
UNION ALL
SELECT a.budget_id,a.parent_id,a.avail_amount,b.ID FROM t_Budget AS a INNER JOIN CTET AS b ON b.budget_id=a.parent_id
)

SELECT  a.* ,
        b.avail_amount AS [各节点总金额]
FROM    t_Budget AS a
        INNER JOIN ( SELECT ID ,
                            SUM(avail_amount) AS avail_amount
                     FROM   CTET
                     GROUP BY ID
                   ) AS b ON a.budget_id = b.ID

查parent_id=0 e.g.

;WITH CTET 
AS
(
SELECT budget_id,parent_id,avail_amount,ID=budget_id FROM t_Budget WHERE parent_id=0
UNION ALL
SELECT a.budget_id,a.parent_id,a.avail_amount,b.ID FROM t_Budget AS a INNER JOIN CTET AS b ON b.budget_id=a.parent_id
)

SELECT  a.* ,
        b.avail_amount AS [各节点总金额]
FROM    t_Budget AS a
        INNER JOIN ( SELECT ID ,
                            SUM(avail_amount) AS avail_amount
                     FROM   CTET
                     GROUP BY ID
                   ) AS b ON a.budget_id = b.ID