------------------------------------------------------------------ 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 allselect 2,'天河',1 union allselect 3,'越秀',1 union allselect 4,'白云',1 union allselect 5,'太和',4 union allselect 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 allselect 2,120 union allselect 3,150 union allselect 4,120 union allselect 5,50 union allselect 6,20go;with tas(select id as rowid,id,[名称] from [Area]union allselect rowid,b.id,a.[名称]from t a,[Area] bwhere a.ID=b.上级ID)selectt.rowid as id,t.名称,sum(isnull(a.[BusCount],0)) as [BusCount]from tleft join[AreaBusCount] aon a.AreaID=t.IDgroup byt.rowid,t.名称order by id/*id名称BusCount-----------------------------------1广州5602天河1203越秀1504白云1905太和506人和20*/--查询指定父节点下的所有子节点以及汇总下级数量
USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id)SELECT * FROM DEPTSGO-- 删除演示环境DROP TABLE Dept----CTE的综合应用USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 查询指定部门及其下的所有子部门 -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id),DEPTCHILD AS( -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门 SELECT Dept_id = P.id, C.id, C.parent_id FROM DEPTS P, Dept C WHERE P.id = C.parent_id UNION ALL SELECT P.Dept_id, C.id, C.parent_id FROM DEPTCHILD P, Dept C WHERE P.id = C.parent_id),DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数 SELECT Dept_id, Cnt = COUNT(*) FROM DEPTCHILD GROUP BY Dept_id)SELECT -- JOIN第1,3个CTE,得到最终的查询结果 D.*, ChildDeptCount = ISNULL(DS.Cnt, 0)FROM DEPTS D LEFT JOIN DEPTCHILDCNT DS ON D.id = DS.Dept_idGO
SQL Server2000使用临时表递归
------------------------------------------------------------------ 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 allselect 2,'鸟',1 union allselect 3,'爬行动物',1 union allselect 4,'哺乳动物',1 union allselect 5,'蛇',3 union allselect 6,'蛙',3 union allselect 7,'猫科动物',4 union allselect 8,'狮子',7 union allselect 9,'老虎',7go--SQL 2000使用临时表实现递归--drop table #tblselect *,levels=0 into #tbl from [animal] where [parentid]=0go--开始递归:while @@ROWCOUNT<>0 begininsert #tblselect a.id,a.name,a.parentid,b.levels+1from [animal] ainner join #tbl bon b.id=a.[parentid]where not exists(select 1 from #tbl c where a.id=c.id)endselect * from #tbl--在这个结果中自己筛选吧/*id name parentid levels----------- -------- ----------- -----------1 动物 0 02 鸟 1 13 爬行动物 1 14 哺乳动物 1 15 蛇 3 26 蛙 3 27 猫科动物 4 28 狮子 7 39 老虎 7 3(9 行受影响)*/
