数据累加的问题

[coolbird8]

有一张表
字段为: id,topid,money，islast,name

这样形成一个树形结构，islast= 1表示树形结构为最后一级，最后一级的money有钱的
但是我想让他的上级，上上级，一直上到最上面的级的钱。进行累加。如何做呢。

[misterliwei]

用几行数据，结果是什么。举个例子。

• 打赏
• 举报

回复 赞

[Mac_麦铁锤]

可以declare 一个global variable 来记录钱的总值，然后写一个recursive query来走，每层的时候吧钱加在variable里~ -- i could be wrong

• 打赏
• 举报

回复 赞

[Mac_麦铁锤]

--Test Environment

create table [GPOLAP].[dbo].[UNKNOWN](
id int,
topid int,
money_dec int,
islast int,
name varchar(20)
)

insert into [GPOLAP].[dbo].[UNKNOWN] values(1,0,15,3,'test123')
insert into [GPOLAP].[dbo].[UNKNOWN] values(2,1,35,2,'test123')
insert into [GPOLAP].[dbo].[UNKNOWN] values(4,1,5,2,'test123')
insert into [GPOLAP].[dbo].[UNKNOWN] values(3,2,76,1,'test123')
insert into [GPOLAP].[dbo].[UNKNOWN] values(5,4,43,1,'test123')

--Recursive query via CTE
WITH [UNKNOWN_CTE] AS
( 
SELECT id, topid, money_dec, islast,name
FROM [GPOLAP].[dbo].[UNKNOWN]
WHERE islast = 1 and id = 3 -- assume we are looking for the ancestors of id = 3
UNION ALL
SELECT a.id, a.topid,  a.money_dec, a.islast,a.name
FROM [GPOLAP].[dbo].[UNKNOWN] a 
INNER JOIN [UNKNOWN_CTE] s ON a.id = s.topid
)
select * from [UNKNOWN_CTE]  -- or SUM(money_dec) to get total amount

这个给你做个参考~我没用variable,比较懒

• 打赏
• 举报

回复 赞