代码 1 /*
2 from: http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp
3 http://msdn.microsoft.com/en-us/library/ms186243.aspx
4 http://msdn.microsoft.com/en-us/library/aa175801%28SQL.80%29.aspx
5 SQL Server 2005 Recursion and WITH Clause
6 SQL Server 2005的递归和WITH子句
7 */
8 ---1 Table of Contents Hierarchy
9 set nocount on
10
11 declare @Sample1 table
12 (
13 RecordID int Primary key NOT NULL ,
14 ParentRecordID int,
15 SortOrder int,
16 Description nvarchar(100),
17 Salary money
18 )
19
20 /* Start loading of test data */
21 insert into @Sample1 values(1,null,null,'CEO',10)
22 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
23 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
24 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
25 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
26 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
27 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
28 insert into @Sample1 values(8,4,2,'Some other item',3)
29 insert into @Sample1 values(9,6,1,'Research Analyst',2)
30
31 set nocount off;
32
33 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)
34 as
35 (
36 select RecordID,ParentRecordID,SortOrder,Salary,
37 convert(varchar(100),'') TOC
38 from @Sample1
39 where ParentRecordID is null
40 union all
41 select R1.RecordID,
42 R1.ParentRecordID,
43 R1.SortOrder,
44 R1.Salary,
45 case when DataLength(R2.TOC) > 0
46 then convert(varchar(100),R2.TOC + '.'
47 + cast(R1.SortOrder as varchar(10)))
48 else convert(varchar(100),
49 cast(R1.SortOrder as varchar(10)))
50 end as TOC
51 from @Sample1 as R1
52 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
53 )
54
55 select * from RecursionCTE order by ParentRecordID,SortOrder asc
56
57 --2 Sum Up Subordinate Salaries of All Employees
58
59 set nocount on
60
61 declare @Sample1 table
62 (
63 RecordID int Primary key NOT NULL ,
64 ParentRecordID int,
65 SortOrder int,
66 Description nvarchar(100),
67 Salary money
68 )
69
70 /* Start loading of test data */
71 insert into @Sample1 values(1,null,null,'CEO',10)
72 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
73 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
74 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
75 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
76 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
77 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
78 insert into @Sample1 values(8,4,2,'Some other item',3)
79 insert into @Sample1 values(9,6,1,'Research Analyst',2)
80
81 set nocount off;
82
83 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
84 as
85 (
86 select RecordID,ParentRecordID,SortOrder,Salary
87 from @Sample1
88 where ParentRecordID is null
89 union all
90 select R1.RecordID,
91 R1.ParentRecordID,
92 R1.SortOrder,
93 R1.Salary
94 from @Sample1 as R1
95 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
96 )
97 select sum(R1.salary) as Salary
98 from @Sample1 as R1
99 JOIN RecursionCTE as R2
100 on R1.RecordID = R2.RecordID
101 --3 Sum Up Subordinate Salaries of a Specific Employee
102 set nocount on
103
104 declare @Sample1 table
105 (
106 RecordID int Primary key NOT NULL ,
107 ParentRecordID int,
108 SortOrder int,
109 Description nvarchar(100),
110 Salary money
111 )
112
113 /* Start loading of test data */
114 insert into @Sample1 values(1,null,null,'CEO',10)
115 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
116 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
117 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
118 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
119 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
120 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
121 insert into @Sample1 values(8,4,2,'Some other item',3)
122 insert into @Sample1 values(9,6,1,'Research Analyst',2)
123
124 set nocount off;
125
126 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
127 as
128 (
129 select RecordID,ParentRecordID,SortOrder,Salary
130 from @Sample1
131 where ParentRecordID =2 -- specific employee id
132 union all
133 select R1.RecordID,
134 R1.ParentRecordID,
135 R1.SortOrder,
136 R1.Salary
137 from @Sample1 as R1
138 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
139 )
140 select sum(R1.salary) as Salary
141 from @Sample1 as R1
142 JOIN RecursionCTE as R2
143 on R1.RecordID = R2.RecordID
144
145 --4 Manager to Subordinate Salary Differential
146
147 set nocount on
148
149 declare @Sample1 table
150 (
151 RecordID int Primary key NOT NULL ,
152 ParentRecordID int,
153 SortOrder int,
154 Description nvarchar(100),
155 Salary money
156 )
157
158 /* Start loading of test data */
159 insert into @Sample1 values(1,null,null,'CEO',10)
160 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
161 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
162 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
163 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
164 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
165 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
166 insert into @Sample1 values(8,4,2,'Some other item',3)
167 insert into @Sample1 values(9,6,1,'Research Analyst',2)
168
169 set nocount off;
170
171 with RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential)
172 as
173 (
174 select RecordID,ParentRecordID,SortOrder,
175 convert(money,null) as ParentSalary,
176 Salary,
177 convert(money,null) as Differential
178 from @Sample1
179 where ParentRecordID is null
180 union all
181 select R1.RecordID,
182 R1.ParentRecordID,
183 R1.SortOrder,
184 convert(money,R2.Salary) as ParentSalary,
185 R1.Salary,
186 convert(money,R2.Salary - R1.Salary) as Differential
187 from @Sample1 as R1
188 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
189
190
191 )
192
193 select * from RecursionCTE order by ParentRecordID,SortOrder asc
2 from: http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp
3 http://msdn.microsoft.com/en-us/library/ms186243.aspx
4 http://msdn.microsoft.com/en-us/library/aa175801%28SQL.80%29.aspx
5 SQL Server 2005 Recursion and WITH Clause
6 SQL Server 2005的递归和WITH子句
7 */
8 ---1 Table of Contents Hierarchy
9 set nocount on
10
11 declare @Sample1 table
12 (
13 RecordID int Primary key NOT NULL ,
14 ParentRecordID int,
15 SortOrder int,
16 Description nvarchar(100),
17 Salary money
18 )
19
20 /* Start loading of test data */
21 insert into @Sample1 values(1,null,null,'CEO',10)
22 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
23 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
24 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
25 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
26 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
27 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
28 insert into @Sample1 values(8,4,2,'Some other item',3)
29 insert into @Sample1 values(9,6,1,'Research Analyst',2)
30
31 set nocount off;
32
33 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)
34 as
35 (
36 select RecordID,ParentRecordID,SortOrder,Salary,
37 convert(varchar(100),'') TOC
38 from @Sample1
39 where ParentRecordID is null
40 union all
41 select R1.RecordID,
42 R1.ParentRecordID,
43 R1.SortOrder,
44 R1.Salary,
45 case when DataLength(R2.TOC) > 0
46 then convert(varchar(100),R2.TOC + '.'
47 + cast(R1.SortOrder as varchar(10)))
48 else convert(varchar(100),
49 cast(R1.SortOrder as varchar(10)))
50 end as TOC
51 from @Sample1 as R1
52 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
53 )
54
55 select * from RecursionCTE order by ParentRecordID,SortOrder asc
56
57 --2 Sum Up Subordinate Salaries of All Employees
58
59 set nocount on
60
61 declare @Sample1 table
62 (
63 RecordID int Primary key NOT NULL ,
64 ParentRecordID int,
65 SortOrder int,
66 Description nvarchar(100),
67 Salary money
68 )
69
70 /* Start loading of test data */
71 insert into @Sample1 values(1,null,null,'CEO',10)
72 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
73 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
74 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
75 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
76 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
77 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
78 insert into @Sample1 values(8,4,2,'Some other item',3)
79 insert into @Sample1 values(9,6,1,'Research Analyst',2)
80
81 set nocount off;
82
83 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
84 as
85 (
86 select RecordID,ParentRecordID,SortOrder,Salary
87 from @Sample1
88 where ParentRecordID is null
89 union all
90 select R1.RecordID,
91 R1.ParentRecordID,
92 R1.SortOrder,
93 R1.Salary
94 from @Sample1 as R1
95 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
96 )
97 select sum(R1.salary) as Salary
98 from @Sample1 as R1
99 JOIN RecursionCTE as R2
100 on R1.RecordID = R2.RecordID
101 --3 Sum Up Subordinate Salaries of a Specific Employee
102 set nocount on
103
104 declare @Sample1 table
105 (
106 RecordID int Primary key NOT NULL ,
107 ParentRecordID int,
108 SortOrder int,
109 Description nvarchar(100),
110 Salary money
111 )
112
113 /* Start loading of test data */
114 insert into @Sample1 values(1,null,null,'CEO',10)
115 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
116 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
117 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
118 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
119 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
120 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
121 insert into @Sample1 values(8,4,2,'Some other item',3)
122 insert into @Sample1 values(9,6,1,'Research Analyst',2)
123
124 set nocount off;
125
126 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
127 as
128 (
129 select RecordID,ParentRecordID,SortOrder,Salary
130 from @Sample1
131 where ParentRecordID =2 -- specific employee id
132 union all
133 select R1.RecordID,
134 R1.ParentRecordID,
135 R1.SortOrder,
136 R1.Salary
137 from @Sample1 as R1
138 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
139 )
140 select sum(R1.salary) as Salary
141 from @Sample1 as R1
142 JOIN RecursionCTE as R2
143 on R1.RecordID = R2.RecordID
144
145 --4 Manager to Subordinate Salary Differential
146
147 set nocount on
148
149 declare @Sample1 table
150 (
151 RecordID int Primary key NOT NULL ,
152 ParentRecordID int,
153 SortOrder int,
154 Description nvarchar(100),
155 Salary money
156 )
157
158 /* Start loading of test data */
159 insert into @Sample1 values(1,null,null,'CEO',10)
160 insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
161 insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
162 insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
163 insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
164 insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
165 insert into @Sample1 values(7,4,1,'Human Resources Director',4)
166 insert into @Sample1 values(8,4,2,'Some other item',3)
167 insert into @Sample1 values(9,6,1,'Research Analyst',2)
168
169 set nocount off;
170
171 with RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential)
172 as
173 (
174 select RecordID,ParentRecordID,SortOrder,
175 convert(money,null) as ParentSalary,
176 Salary,
177 convert(money,null) as Differential
178 from @Sample1
179 where ParentRecordID is null
180 union all
181 select R1.RecordID,
182 R1.ParentRecordID,
183 R1.SortOrder,
184 convert(money,R2.Salary) as ParentSalary,
185 R1.Salary,
186 convert(money,R2.Salary - R1.Salary) as Differential
187 from @Sample1 as R1
188 join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
189
190
191 )
192
193 select * from RecursionCTE order by ParentRecordID,SortOrder asc