日期:2014-05-16 浏览次数:20649 次
----------------------------------------------------------------
-- 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
----------------结果----------------------------
/*