Recursive sum in parent-child hierarchy T-SQL

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96

---树形（父子关系类）分级类统计（父子统计） --涂聚文 2014-08-14 drop table BookKindList   create table BookKindList (     BookKindID INT IDENTITY(1,1) PRIMARY KEY,     BookKindName nvarchar(500) not null,     BookKindParent int null ) GO   drop table BookCostsPer --- CREATE TABLE BookCostsPer (   ID INT IDENTITY(1,1) PRIMARY KEY,   NodeId INT NOT NULL,   [BookName] nvarchar(500) NOT NULL,   [CostsValue] DECIMAL(18,6) NOT NULL,   CostDate datetime default(getdate()) ) go   select * from BookKindList   insert into BookKindList(BookKindName,BookKindParent) values('塗聚文书目录',null) insert into BookKindList(BookKindName,BookKindParent) values('文学',1) insert into BookKindList(BookKindName,BookKindParent) values('设计艺术',1) insert into BookKindList(BookKindName,BookKindParent) values('自然科学',1) insert into BookKindList(BookKindName,BookKindParent) values('小说',2) insert into BookKindList(BookKindName,BookKindParent) values('诗词散曲',2)     insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(3,'设计理论',450,'2014-01-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(4,'计算机科学',400,'2014-01-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(5,'傲慢與偏見',550,'2014-01-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(6,'宋词',150,'2014-01-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(3,'版式设计',150,'2013-05-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(4,'C语言设计',200,'2013-05-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(5,'汤姆叔叔的小屋',530,'2013-05-02') insert into BookCostsPer(NodeId,[BookName],[CostsValue],CostDate) values(6,'唐诗',110,'2013-05-02')   --视图 create view v_BookCostsPer as select *,year(CostDate) as 'YearName' from BookCostsPer go     ---統計 WITH DirectReport (BookKindParent, BookKindID, [BookKindName], LEVEL, Struc) AS ( -- anchor SELECT a.BookKindParent, a.BookKindID, a.BookKindName, 0 AS LEVEL, cast(':' + cast(a.BookKindID AS varchar) + ':' AS varchar (100))  AS Struc FROM BookKindList a WHERE a.BookKindParent IS NULL UNION ALL -- recursive SELECT a.BookKindParent, a.BookKindID, a.BookKindName, LEVEL +1, cast(d.Struc + cast(a.BookKindID AS varchar)+ ':'  AS varchar(100)) AS Struc FROM BookKindList a   JOIN DirectReport d ON d.BookKindID = a.BookKindParent ) SELECT d.BookKindParent, d.BookKindID, d.BookKindName, d.level, d.Struc, sum(CASE WHEN d.Struc = SUBSTRING(dd.Struc, 1, len(d.Struc))THEN c.CostsValue ELSE 0 END) AS    TotCost FROM DirectReport d,DirectReport dd JOIN BookCostsPer c ON c.NodeId = dd.BookKindID GROUP BY d.BookKindParent,d.BookKindID, d.BookKindName, d.level, d.Struc ORDER BY  d.BookKindID GO     -----按年各父子类合计 with DirectReport (BookKindParent, BookKindID, [BookKindName], Level, Struc, [YearName]) as (   -- anchor   select a.BookKindParent, a.BookKindID, a.BookKindName, 0 as Level, cast(':' + cast(a.BookKindID as varchar) + ':' as varchar (100))  as Struc, y.[YearName]   from BookKindList a, YearNames y   where a.BookKindParent is null   union all   -- recursive   Select a.BookKindParent, a.BookKindID, a.BookKindName, Level +1, cast(d.Struc + cast(a.BookKindID as varchar)+ ':'  as varchar(100)) as Struc, d.[YearName]   from BookKindList a     join DirectReport d on d.BookKindID = a.BookKindParent   )   Select d.BookKindParent, d.[YearName], d.BookKindID, d.BookKindName, d.level, d.Struc,-- dd.Struc, sum(case when d.Struc = SUBSTRING(dd.Struc, 1, len(d.Struc))then c.CostsValue else 0 end) as TotCost from DirectReport d   left join DirectReport dd on d.[YearName] = dd.[YearName]   join v_BookCostsPer c on c.[YearName] = dd.[YearName] and c.NodeId = dd.BookKindID  group by d.BookKindParent, d.[YearName], d.BookKindID, d.BookKindName, d.level, d.Struc order by  d.[YearName], d.BookKindID GO