有一张表
字段为: id,topid,money，islast,name
这样形成一个树形结构，islast= 1表示树形结构为最后一级，最后一级的money有钱的
但是我想让他的上级，上上级，一直上到最上面的级的钱。进行累加。如何做呢。
------解决思路----------------------
用几行数据，结果是什么。举个例子。
------解决思路----------------------
可以declare 一个global variable 来记录钱的总值，然后写一个recursive query来走，每层的时候吧钱加在variable里~
-- i could be wrong
------解决思路----------------------
--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,比较懒