树形结构逐级汇总查询

 1.

--测试数据
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT '101'  ,'现金'
UNION ALL SELECT '102'  ,'银行存款'
UNION ALL SELECT '10201','工行'
UNION ALL SELECT '10202','建行'
UNION ALL SELECT '10203','农行'

DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT '101'  ,100
UNION ALL SELECT '10201',20
UNION ALL SELECT '10202',120

--逐级汇总查询
SELECT a.No,a.Name,
 [Money]=ISNULL(SUM([Money]),0)
FROM @a a
 LEFT JOIN @b b ON b.No LIKE a.No+'%'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No         Name        Money
---------------- ----------------- ---------------------
101        现金         100.0000
102        银行存款     140.0000
10201      工行         20.0000
10202      建行         120.0000
10203      农行         .0000
--*/

2.

create table cat(catId int,catName nvarchar(40),parentId int)
create table cat_money(catId int,money int)
insert into cat select '1','a','0'
union all
select '2','b','1'
union all
select '3','c','1'
union all
select '4','d','2'
union all
select '5','e','3'

insert into cat_money select '1','10'
union all
select '2','20'
union all
select '3','30'
union all
select '4','40'
union all
select '5','50'
go

Create FUNCTION fn_catid()
RETURNS @t TABLE(ID int,Level int,SID varchar(8000))
AS
BEGIN
 DECLARE @Level int
 SET @Level=1
 INSERT @t SELECT catID,@Level,','+CAST(catID as varchar)+','
 FROM cat
 WHERE parentId=0
 WHILE @@ROWCOUNT>0
 BEGIN
  SET @Level=@Level+1
  INSERT @t SELECT a.catID,@Level,b.SID+CAST(a.catID as varchar)+','
  FROM cat a,@t b
  WHERE a.ParentId=b.ID
   AND b.Level=@Level-1
 END
 RETURN
END

GO

SELECT a.catID,a.parentId,SUM_Money=SUM(b.Money)
FROM cat a,fn_catid() a1,
 cat_Money b,fn_catid() b1
WHERE a.catID=a1.ID
 AND b.catID=b1.ID
 AND b1.SID LIKE a1.SID+'%'
GROUP BY a.catID,a.parentId