BOM子节点汇总数据

节点汇总数据"/>

BOM子节点汇总数据

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休,欲语泪先流!)
-- Date    :2012-12-06 10:44:59
-- Version:--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) --	Feb 10 2012 19:13:17 --	Copyright (c) Microsoft Corporation--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)--
----------------------------------------------------------------
--> 测试数据：[Area]--BOM子节点汇总问题
if object_id('[Area]') is not null 
drop table [Area]
go 
create table [Area]
([ID] int,[名称] varchar(4),[上级ID] int
)
insert [Area]
select 1,'广州',null union all
select 2,'天河',1 union all
select 3,'越秀',1 union all
select 4,'白云',1 union all
select 5,'太和',4 union all
select 6,'人和',4
--> 测试数据：[AreaBusCount]
if object_id('[AreaBusCount]') is not null 
drop table [AreaBusCount]
go 
create table [AreaBusCount]
([AreaID] int,[BusCount] int
)
insert [AreaBusCount]
select 1,100 union all
select 2,120 union all
select 3,150 union all
select 4,120 union all
select 5,50 union all
select 6,20
go;with t
as(
select id as rowid,id,[名称] 
from  [Area]
union all
select rowid,b.id,a.[名称]
from t a,[Area] b
where a.ID=b.上级ID
)
selectt.rowid  as id,t.名称,sum(isnull(a.[BusCount],0)) as [BusCount]
from t
left join[AreaBusCount] a
on a.AreaID=t.ID
group byt.rowid,t.名称
order by id
/*
id	名称	BusCount
-----------------------------------
1	广州	560
2	天河	120
3	越秀	150
4	白云	190
5	太和	50
6	人和	20
*/

--查询指定父节点下的所有子节点以及汇总下级数量

USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(id int PRIMARY KEY, parent_id int,name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(-- 定位点成员SELECT * FROM DeptWHERE name = @Dept_nameUNION ALL-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归SELECT A.*FROM Dept A, DEPTS BWHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO-- 删除演示环境
DROP TABLE Dept----CTE的综合应用USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(id int PRIMARY KEY, parent_id int,name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(   -- 查询指定部门及其下的所有子部门-- 定位点成员SELECT * FROM DeptWHERE name = @Dept_nameUNION ALL-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归SELECT A.*FROM Dept A, DEPTS BWHERE A.parent_id = B.id
),
DEPTCHILD AS(  -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门SELECT Dept_id = P.id, C.id, C.parent_idFROM DEPTS P, Dept CWHERE P.id = C.parent_idUNION ALLSELECT P.Dept_id, C.id, C.parent_idFROM DEPTCHILD P, Dept CWHERE P.id = C.parent_id
),
DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数SELECT Dept_id, Cnt = COUNT(*)FROM DEPTCHILDGROUP BY Dept_id
)
SELECT    -- JOIN第1,3个CTE,得到最终的查询结果D.*,ChildDeptCount = ISNULL(DS.Cnt, 0)
FROM DEPTS DLEFT JOIN DEPTCHILDCNT DSON D.id = DS.Dept_id
GO

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休,欲语泪先流!)
-- Date    :2012-12-06 13:02:57
-- Version:--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) --	Feb 10 2012 19:13:17 --	Copyright (c) Microsoft Corporation--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)--
----------------------------------------------------------------
--> 测试数据：[animal]
if object_id('[animal]') is not null 
drop table [animal]
go 
create table [animal]
([id] int,[name] varchar(8),[parentid] int
)
insert [animal]
select 1,'动物',0 union all
select 2,'鸟',1 union all
select 3,'爬行动物',1 union all
select 4,'哺乳动物',1 union all
select 5,'蛇',3 union all
select 6,'蛙',3 union all
select 7,'猫科动物',4 union all
select 8,'狮子',7 union all
select 9,'老虎',7
go--SQL 2000使用临时表实现递归
--drop table #tbl
select *,levels=0 into #tbl from [animal] where [parentid]=0
go--开始递归:
while @@ROWCOUNT<>0 
begin
insert #tbl
select 
a.id,a.name,a.parentid,b.levels+1
from [animal] a
inner join #tbl b
on b.id=a.[parentid]
where not exists(select 1 from #tbl c where a.id=c.id)
end
select * from #tbl--在这个结果中自己筛选吧
/*
id          name     parentid    levels
----------- -------- ----------- -----------
1           动物       0           0
2           鸟        1           1
3           爬行动物     1           1
4           哺乳动物     1           1
5           蛇        3           2
6           蛙        3           2
7           猫科动物     4           2
8           狮子       7           3
9           老虎       7           3(9 行受影响)*/