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

 

posted @   ®Geovin Du Dream Park™  阅读(357)  评论(0编辑  收藏  举报
编辑推荐:
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
阅读排行:
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!
历史上的今天:
2012-08-14 Csharp run sql script create database
< 2025年2月 >
26 27 28 29 30 31 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 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示