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 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 行受影響)*/