SQL Server CET 通用表表达式 之 精典递归实战

----------------------------------------------------------------------------------
-- Subject     : CET 通用表表达式 之 精典递归实战
-- Author      : 柳永法(yongfa365) http://www.yongfa365.com/ cto@yongfa365.com
-- CreateDate  : 2010-05-03 11:09:46
-- Environment : Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--               Nov 24 2008 13:01:59 
--               Copyright (c) 1988-2005 Microsoft Corporation
--               Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-- Reference   : http://msdn.microsoft.com/zh-cn/magazine/cc163346.aspx#S1
----------------------------------------------------------------------------------


IF OBJECT_ID('Dept') IS NOT NULL 
DROP TABLE Dept 

go

CREATE TABLE Dept (Id INT , ParentId INT , DeptName NVARCHAR(10))

INSERT Dept SELECT 1 ,   0 , N'食品'
  UNION ALL SELECT 2 ,   1 , N'水果'
  UNION ALL SELECT 3 ,   1 , N'蔬菜'
  UNION ALL SELECT 4 ,   2 , N'香蕉'
  UNION ALL SELECT 5 ,   2 , N'苹果'
  UNION ALL SELECT 6 ,   3 , N'青菜'
  
  UNION ALL SELECT 11 ,  0 , N'计算机'
  UNION ALL SELECT 12 , 11 , N'软件'
  UNION ALL SELECT 13 , 11 , N'硬件'
  UNION ALL SELECT 14 , 12 , N'Office'
  UNION ALL SELECT 15 , 12 , N'Emeditor'
  UNION ALL SELECT 16 , 13 , N'内存'


--得到所有水果
;WITH cte AS
(
	SELECT Id,ParentId,DeptName FROM Dept WHERE id=2
	UNION ALL
	SELECT a.Id,a.ParentId,a.DeptName FROM Dept a,cte b WHERE a.ParentId=b.Id
)

SELECT * FROM cte

-- Id          ParentId    DeptName
-- ----------- ----------- ----------
-- 2           1           水果
-- 4           2           香蕉
-- 5           2           苹果
-- 
-- (3 行受影响)


--得到当前及所有父级
;WITH cte AS
(
	SELECT Id,ParentId,DeptName FROM Dept WHERE id=16
	UNION ALL
	SELECT a.Id,a.ParentId,a.DeptName FROM Dept a,cte b WHERE a.Id=b.ParentId
)

SELECT * FROM cte

-- Id          ParentId    DeptName
-- ----------- ----------- ----------
-- 16          13          内存
-- 13          11          硬件
-- 11          0           计算机
-- 
-- (3 行受影响)

--得到所有路径
;WITH cte AS
(
	SELECT Id,ParentId,DeptName,Path=CAST(DeptName AS VARCHAR(MAX)) FROM Dept WHERE parentid=0
	UNION ALL
	SELECT a.Id,a.ParentId,a.DeptName,CAST(b.Path +'/'+a.DeptName AS VARCHAR(MAX)) FROM Dept a,cte b WHERE a.ParentId=b.Id
)

SELECT * FROM cte

-- Id          ParentId    DeptName   Path
-- ----------- ----------- ---------- -------------------------
-- 1           0           食品         食品
-- 11          0           计算机       计算机
-- 12          11          软件         计算机/软件
-- 13          11          硬件         计算机/硬件
-- 16          13          内存         计算机/硬件/内存
-- 14          12          Office       计算机/软件/Office
-- 15          12          Emeditor     计算机/软件/Emeditor
-- 2           1           水果         食品/水果
-- 3           1           蔬菜         食品/蔬菜
-- 6           3           青菜         食品/蔬菜/青菜
-- 4           2           香蕉         食品/水果/香蕉
-- 5           2           苹果         食品/水果/苹果
-- 
-- (12 行受影响)