Id parentId Bail 得到这一列
7 0 0.00 220.00
8 7 100.00 220.00
9 8 100.00 100.00
10 8 20.00 20.00
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-04-03 15:56:28
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[parentId] int,[Bail] numeric(5,2))
insert [tb]
select 7,0,0.00 union all
select 8,7,100.00 union all
select 9,8,100.00 union all
select 10,8,20.00
--------------开始查询--------------------------
;WITH f AS
(
SELECT *,1 AS LEVEL FROM tb AS a WHERE NOT EXISTS(SELECT 1 FROM tb WHERE id=a.parentId)
UNION ALL
SELECT a.*,level+1 FROM tb AS a INNER JOIN f AS b ON a.parentId=b.id
)
SELECT *,ISNULL((SELECT SUM(Bail) AS Bail FROM f WHERE level>a.level),bail) AS SUMBail FROM f AS a
----------------结果----------------------------
/* Id parentId Bail LEVEL Bail
----------- ----------- --------------------------------------- ----------- ---------------------------------------
7 0 0.00 1 220.00
8 7 100.00 2 120.00
9 8 100.00 3 100.00
10 8 20.00 3 20.00