22,301
社区成员




--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,比较懒