parent-childRecursive sum in parent-child hierarchy T-SQL

---树形(父子关系类)分级类统计(父子统计)
--涂聚文 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
时间: 2024-09-23 11:09:39

parent-childRecursive sum in parent-child hierarchy T-SQL的相关文章

IE8中"HTML Parsing Error:Unable to modify the parent container element before the child

 一. 又涨见识了 IE8报下面错误,而且是我的机器不报,同事的机器报,试了4台,两个报,两个不报,IE版本都一样,没想明白 解决: 1.查看是否有未关闭的html标签,比如<table>而没有</table> (我就是这个原因) 2.是否在页面未加载完前js代码操作了body里的元素,将相关js代码移到</body>后面 网页错误详细信息 用户代理: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/

sql中count或sum为条件的查询示例(sql查询count)_MsSql

比如user_num表: 例1:查询出现过2次的user 往往初学者会错误地认为在where 语句里直接使用count()算法,很显然这个想法是错误的,count()方法并不能被用在where子句中,为了解决问题,我们可以在group by子句后面使用HAVING来做条件限制.错误做法:select * from user_num where count(user)>=2 group by user;正确做法:select * from user_num group by user HAVING

sql中count或sum为条件的查询示例(sql查询count)

比如user_num表: 例1:查询出现过2次的user 往往初学者会错误地认为在where 语句里直接使用count()算法,很显然这个想法是错误的,count()方法并不能被用在where子句中,为了解决问题,我们可以在group by子句后面使用HAVING来做条件限制.错误做法:select * from user_num where count(user)>=2 group by user;正确做法:select * from user_num group by user HAVING

[NHibernate]Parent/Child

系列文章 [Nhibernate]体系结构 [NHibernate]ISessionFactory配置 [NHibernate]持久化类(Persistent Classes) [NHibernate]O/R Mapping基础 [NHibernate]集合类(Collections)映射  [NHibernate]关联映射 引言  刚刚接触NHibernate的人大多是从父子关系(parent/child type relationship)的建模入手的.父子关系的建模有两种方法.比较简便.直

PHP5中的this,self和parent关键字详解

php5|关键字|详解 PHP5是一具备了大部分面向对象语言的特性的语言,比PHP4有了很多的面向对象的特性,但是有部分概念也比较绕人,所以今天拿出来说说,说的不好,请高手见谅. (阅读本文,需要了解PHP5的面向对象的知识) 首先我们来明白上面三个关键字: this,self,parent,从字面上比较好理解,是指这,自己,父亲,呵呵,比较好玩了,我们先建立几个概念,这三个关键字分别是用在什么地方呢?我们初步解释一下,this是指向当前对象的指针(我们姑且用C里面的指针来看吧),self是指向

PHP5中的this,self和parent关键字详解教程_php基础

首先我们来明白上面三个关键字: this,self,parent,从字面上比较好理解,是指这,自己,父亲,呵呵,比较好玩了,我们先建立几个概念,这三个关键字分别是用在什么地方呢?我们初步解释一下,this是指向当前对象的指针(我们姑且用C里面的指针来看吧),self是指向当前类的指针,parent是指向父类的指针.我们这里频繁使用指针来描述,是因为没有更好的语言来表达,呵呵,语文没学好. -_-# 这么说还不能很了解,那我们就根据实际的例子结合来讲讲. (1) this 复制代码 代码如下: <

PHP编程过程中需要了解的this,self,parent的区别_php技巧

{一}PHP中this,self,parent的区别之一this篇 面向对象编程(OOP,Object Oriented Programming)现已经成为编程人员的一项基本技能.利用OOP的思想进行PHP的高级编程,对于提高PHP编程能力和规划web开发构架都是很有意义的. PHP5经过重写后,对OOP的支持额有了很大的飞跃,成为了具备了大部分面向对象语言的特性的语言,比PHP4有了很多的面向对象的特性.这里我主要谈的是 this,self,parent 三个关键字之间的区别.从字面上来理解,

探讨PHP中this,self,parent的区别详解_php技巧

{一}PHP中this,self,parent的区别之一this篇面向对象编程(OOP,Object OrientedProgramming)现已经成为编程人员的一项基本技能.利用OOP的思想进行PHP的高级编程,对于提高PHP编程能力和规划web开发构架都是很有意义的.PHP5经过重写后,对OOP的支持额有了很大的飞跃,成为了具备了大部分面向对象语言的特性的语言,比PHP4有了很多的面向对象的特性.这里我主要谈的是this,self,parent 三个关键字之间的区别.从字面上来理解,分别是指

window.parent与window.openner用法与区别

frame框架里的页面要改其他同框架下的页面或父框架的页面就用parent window.opener引用的是window.open打开的页面的父页面. window.frames对象可以引用iframe里的页面,也可以引用frameset里的页面. 可以这样  代码如下 复制代码 window.frames[0].document.getElementById('xx'); 可以这样  代码如下 复制代码 window.frames[0].document.body.innerHTML; fr

有关jQuery中parent()和siblings()的小问题_jquery

今天发现一个小问题,现在也不知道到底是哪个梗在作祟,但是感觉是parent()和siblings()其中的一个. 我是想这样的根据输入的条件删选内容: demo: <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Document</title> <script src="http://co