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

view plain copy to clipboard print ?

1. ----------------------------------------------------------------------------------   
2. -- Subject     : CET 通用表表达式 之 精典递归实战   
3. -- Author      : 柳永法(yongfa365) http://www.yongfa365.com/ cto@yongfa365.com   
4. -- CreateDate  : 2010-05-03 11:09:46   
5. -- Environment : Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)    
6. --               Nov 24 2008 13:01:59    
7. --               Copyright (c) 1988-2005 Microsoft Corporation   
8. --               Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)   
9. -- Reference   : http://msdn.microsoft.com/zh-cn/magazine/cc163346.aspx#S1   
10. ----------------------------------------------------------------------------------   
11.   
12.   
13. IF OBJECT_ID('Dept') IS NOT NULL    
14. DROP TABLE Dept    
15.   
16. go   
17.   
18. CREATE TABLE Dept (Id INT , ParentId INT , DeptName NVARCHAR(10))   
19.   
20. INSERT Dept SELECT 1 ,   0 , N'食品'  
21.   UNION ALL SELECT 2 ,   1 , N'水果'  
22.   UNION ALL SELECT 3 ,   1 , N'蔬菜'  
23.   UNION ALL SELECT 4 ,   2 , N'香蕉'  
24.   UNION ALL SELECT 5 ,   2 , N'苹果'  
25.   UNION ALL SELECT 6 ,   3 , N'青菜'  
26.      
27.   UNION ALL SELECT 11 ,  0 , N'计算机'  
28.   UNION ALL SELECT 12 , 11 , N'软件'  
29.   UNION ALL SELECT 13 , 11 , N'硬件'  
30.   UNION ALL SELECT 14 , 12 , N'Office'  
31.   UNION ALL SELECT 15 , 12 , N'Emeditor'  
32.   UNION ALL SELECT 16 , 13 , N'内存'  
33.   
34.   
35. --得到所有水果   
36. ;WITH cte AS  
37. (   
38.     SELECT Id,ParentId,DeptName FROM Dept WHERE id=2   
39.     UNION ALL  
40.     SELECT a.Id,a.ParentId,a.DeptName FROM Dept a,cte b WHERE a.ParentId=b.Id   
41. )   
42.   
43. SELECT * FROM cte   
44.   
45. -- Id          ParentId    DeptName   
46. -- ----------- ----------- ----------   
47. -- 2           1           水果   
48. -- 4           2           香蕉   
49. -- 5           2           苹果   
50. --    
51. -- (3 行受影响)   
52.   
53.   
54. --得到当前及所有父级   
55. ;WITH cte AS  
56. (   
57.     SELECT Id,ParentId,DeptName FROM Dept WHERE id=16   
58.     UNION ALL  
59.     SELECT a.Id,a.ParentId,a.DeptName FROM Dept a,cte b WHERE a.Id=b.ParentId   
60. )   
61.   
62. SELECT * FROM cte   
63.   
64. -- Id          ParentId    DeptName   
65. -- ----------- ----------- ----------   
66. -- 16          13          内存   
67. -- 13          11          硬件   
68. -- 11          0           计算机   
69. --    
70. -- (3 行受影响)   
71.   
72. --得到所有路径   
73. ;WITH cte AS  
74. (   
75.     SELECT Id,ParentId,DeptName,Path=CAST(DeptName AS VARCHAR(MAX)) FROM Dept WHERE parentid=0   
76.     UNION ALL  
77.     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   
78. )   
79.   
80. SELECT * FROM cte   
81.   
82. -- Id          ParentId    DeptName   Path   
83. -- ----------- ----------- ---------- -------------------------   
84. -- 1           0           食品         食品   
85. -- 11          0           计算机       计算机   
86. -- 12          11          软件         计算机/软件   
87. -- 13          11          硬件         计算机/硬件   
88. -- 16          13          内存         计算机/硬件/内存   
89. -- 14          12          Office       计算机/软件/Office   
90. -- 15          12          Emeditor     计算机/软件/Emeditor   
91. -- 2           1           水果         食品/水果   
92. -- 3           1           蔬菜         食品/蔬菜   
93. -- 6           3           青菜         食品/蔬菜/青菜   
94. -- 4           2           香蕉         食品/水果/香蕉   
95. -- 5           2           苹果         食品/水果/苹果   
96. --    
97. -- (12 行受影响)  

----------------------------------------------------------------------------------
-- 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 行受影响)

 

引用本页地址：http://www.yongfa365.com/item/SQL-Server-CET-Recursive.html