parent-childRecursive sum in parent-child hierarchy T-SQL
来源:互联网 发布:网络棋牌大厅 编辑:程序博客网 时间:2024/05/16 02:24
---树形(父子关系类)分级类统计(父子统计)--涂聚文 2014-08-14drop table BookKindListcreate table BookKindList( BookKindID INT IDENTITY(1,1) PRIMARY KEY, BookKindName nvarchar(500) not null, BookKindParent int null)GOdrop 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()))goselect * from BookKindListinsert 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_BookCostsPerasselect *,year(CostDate) as 'YearName' from BookCostsPergo---統計WITH DirectReport (BookKindParent, BookKindID, [BookKindName], LEVEL, Struc)AS(-- anchorSELECT a.BookKindParent, a.BookKindID, a.BookKindName, 0 AS LEVEL, cast(':' + cast(a.BookKindID AS varchar) + ':' AS varchar (100)) AS StrucFROM BookKindList aWHERE a.BookKindParent IS NULLUNION ALL-- recursiveSELECT a.BookKindParent, a.BookKindID, a.BookKindName, LEVEL +1, cast(d.Struc + cast(a.BookKindID AS varchar)+ ':' AS varchar(100)) AS StrucFROM 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 TotCostFROM DirectReport d,DirectReport ddJOIN BookCostsPer c ON c.NodeId = dd.BookKindIDGROUP BY d.BookKindParent,d.BookKindID, d.BookKindName, d.level, d.StrucORDER BY d.BookKindIDGO-----按年各父子类合计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 TotCostfrom 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.Strucorder by d.[YearName], d.BookKindIDGO
0 0
- parent-childRecursive sum in parent-child hierarchy T-SQL
- Recursive sum in parent-child hierarchy T-SQL
- parent > child
- 4 conditionals in JSX&parent child relationships
- 【jQuery】parent > child选择器
- $(“parent > child”)选择器
- 【JQuery】parent > child选择器
- parent-child 关系介绍
- jQuery parent>child
- Parent and child cursors in the library cache
- parent cursor, child cursor以及v$sqlarea, v$sql
- parent
- parent
- Python child, parent, super CLASS
- parent > child选择器(jQuery)
- Angular-Parent/Child Controller Communication
- 2-7parent > child选择器
- Can't resolve reference to bean 'dataSource' in parent factory: no parent factory available
- log4cxx的使用
- android Vibrator使用示例
- Linux 上的基础网络设备详解
- mysql字符集及乱码问题
- Recursive sum in parent-child hierarchy T-SQL
- parent-childRecursive sum in parent-child hierarchy T-SQL
- Numpy教程
- C++ Primer Plus 第六版 8.2 答案
- mysql字符集修改方法&中文乱码问题
- glFlush,glFinish和SwapBuffers用法
- goagent解决Android SDK Manager下载过慢的问题
- C++技术问题总结-第5篇 TCP三次握手
- 黑马视频学习笔记-预处理指令
- Java中的StringTokenizer类的使用方法