---树形（父子关系类）分级类统计（父子统计）

--涂聚文 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