代码
1 create table Employee
2 (
3 Id INT IDENTITY(1,1) PRIMARY KEY,
4 [Name] varchar(30) null,
5 JobTitle varchar(30) null,
6 Manager int null
7 )
8
9 insert Employee
10 select 'incf', 'IT Director',null union all
11 select 'inc3', 'Finance Director',null union all
12 select 'geovindu', 'assces',1 union all
13 select 'du', 'assces',1 union all
14 select 'fa','account',2 union all
15 select 'd','account',2
16
17 WITH OrganisationChart (Id, [Name], JobTitle, Manager) AS
18 (
19 SELECT
20 Id, [Name], JobTitle, Manager
21 FROM dbo.Employee
22 WHERE
23 Manager IS NULL
24 UNION ALL
25 SELECT emp.Id, emp.[Name], emp.JobTitle, emp.Manager
26 FROM dbo.Employee emp
27 INNER JOIN OrganisationChart ON
28 emp.Manager = OrganisationChart.Id
29 )
30 SELECT * FROM OrganisationChart
31
32
33 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager) AS
34 (
35 SELECT
36 Id, [Name], JobTitle, 0, Manager
37 FROM dbo.Employee
38 WHERE
39 Manager IS NULL
40 UNION ALL
41 SELECT emp.Id, emp.[Name], emp.JobTitle, [Level] + 1, emp.Manager
42 FROM dbo.Employee emp
43 INNER JOIN OrganisationChart ON
44 emp.Manager = OrganisationChart.Id
45 )
46 SELECT * FROM OrganisationChart
47 ORDER BY [Level]
48
49
50 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager, [Root]) AS
51 (
52 SELECT Id, [Name], JobTitle, 0, Manager, Id
53 FROM dbo.Employee
54 WHERE Manager IS NULL
55 UNION ALL
56 SELECT emp.Id,emp.[Name],emp.JobTitle,[Level] + 1,emp.Manager,[Root]
57 FROM dbo.Employee emp
58 INNER JOIN OrganisationChart ON
59 emp.Manager = OrganisationChart.Id
60 )
61 SELECT * FROM OrganisationChart
62 --WHERE [Name] = 'incf'
63 WHERE [Root] = 1
64
65
66
67 --示例数据库
68
69 /*
70 递归查询对于同一个表父子关系的计算提供了很大的方便，这个示例使用了SQL server 2005中的递归查询,使用的表是CarParts，这个表存储了一辆汽车的所有零件以及结构，part为零件单位，subpart为子零件，Qty为数量。
71
72 具体示例如下：
73
74 */
75 CREATE TABLE CarParts
76
77 (
78
79 CarID INT NOT NULL,
80
81 Part VARCHAR(15),
82
83 SubPart VARCHAR(15),
84
85 Qty INT
86
87 )
88
89 GO
90
91 INSERT CarParts VALUES (1, 'Body', 'Door', 4)
92
93 INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
94
95 INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
96
97 INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
98
99 INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
100
101 INSERT CarParts VALUES (1, 'Door', 'Window', 1)
102
103 INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
104
105 INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
106
107 INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
108
109 INSERT CarParts VALUES (1, 'Mirror', 'small_Mirror', 4)
110
111 GO
112
113 SELECT * FROM CarParts
114
115 GO
116
117 /*
118
119 一辆汽车需要各个零件的数目
120
121 1个Body 需要4个Door
122
123 1个Door 需要1个Mirror
124
125 那么
126
127 1个body需要4个Mirror
128
129 结构很简单吧
130
131 */
132
133 WITH CarPartsCTE(SubPart, Qty)
134
135 AS
136
137 (
138
139 -- 固定成员 (AM):
140
141 -- SELECT查询无需参考CarPartsCTE
142
143 -- 递归从此处开始
144
145 SELECT SubPart, Qty
146
147 FROM CarParts
148
149 WHERE Part = 'Body'
150
151 UNION ALL
152
153 -- 递归成员 (RM):
154
155 -- SELECT查询参考CarPartsCTE
156
157 -- 使用现有数据往下一层展开
158
159 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
160
161 FROM CarPartsCTE
162
163 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
164
165 WHERE CarParts.CarID = 1
166
167 )
168
169 SELECT SubPart,Qty AS TotalNUM
170
171 FROM CarPartsCTE
172
173 /*
174
175 注意看最下层的small_Mirror 位于 表最后的位置，
176
177 由此可以看出改递归不是开始就进行递归查询而是在1层完全展开后在根据该层展开下一层不是深度优先的递归
178
179 */
180
181 drop table CarParts
182
183
184
185 WITH DirectReports(groupid, member, EmployeeLevel,type) AS
186
187 (
188
189 SELECT groupid, member, 0,type AS EmployeeLevel
190
191 FROM groupinfo
192
193 WHERE groupid = 'finance_company'
194
195 UNION ALL
196
197 SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
198
199 FROM groupinfo e
200
201 INNER JOIN DirectReports d
202
203 ON e.groupid = d.member
204
205 )
206
207 SELECT b.nickname,groupid, member, EmployeeLevel,type
208
209 FROM DirectReports,userbasicinfo b
210
211 where DirectReports.member=b.id
212
213 and type = 1
214
215
216 USE AdventureWorks;
217 GO
218 WITH DirReps(ManagerID, DirectReports) AS
219 (
220 SELECT ManagerID, COUNT(*)
221 FROM HumanResources.Employee AS e
222 WHERE ManagerID IS NOT NULL
223 GROUP BY ManagerID
224 )
225 SELECT ManagerID, DirectReports
226 FROM DirReps
227 ORDER BY ManagerID;
228 GO
229
230
231 WITH DirReps (Manager, DirectReports) AS
232 (
233 SELECT ManagerID, COUNT(*) AS DirectReports
234 FROM HumanResources.Employee
235 GROUP BY ManagerID
236 )
237 SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
238 FROM DirReps
239 WHERE DirectReports>= 2 ;
240 GO
241
242
243
244
245 USE AdventureWorks;
246 GO
247 WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
248 AS
249 (
250 SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
251 FROM Sales.SalesOrderHeader
252 GROUP BY SalesPersonID
253 )
254 SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
255 E.ManagerID, OM.NumberOfOrders, OM.MaxDate
256 FROM HumanResources.Employee AS E
257 JOIN Sales_CTE AS OS
258 ON E.EmployeeID = OS.SalesPersonID
259 LEFT OUTER JOIN Sales_CTE AS OM
260 ON E.ManagerID = OM.SalesPersonID
261 ORDER BY E.EmployeeID;
262 GO
263
264 ---管理員下的下屬
265 USE AdventureWorks;
266 GO
267 WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
268 (
269 SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
270 FROM HumanResources.Employee
271 WHERE ManagerID IS NULL
272 UNION ALL
273 SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
274 FROM HumanResources.Employee e
275 INNER JOIN DirectReports d
276 ON e.ManagerID = d.EmployeeID
277 )
278 SELECT ManagerID, EmployeeID, EmployeeLevel
279 FROM DirectReports
280 WHERE EmployeeLevel <= 2 ;
281 GO
282
283 USE AdventureWorks;
284 GO
285 WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
286 AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
287 e.Title,
288 e.EmployeeID,
289 1,
290 CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
291 FROM HumanResources.Employee AS e
292 JOIN Person.Contact AS c ON e.ContactID = c.ContactID
293 WHERE e.ManagerID IS NULL
294 UNION ALL
295 SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
296 c.FirstName + ' ' + c.LastName),
297 e.Title,
298 e.EmployeeID,
299 EmployeeLevel + 1,
300 CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
301 LastName)
302 FROM HumanResources.Employee as e
303 JOIN Person.Contact AS c ON e.ContactID = c.ContactID
304 JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
305 )
306 SELECT EmployeeID, Name, Title, EmployeeLevel
307 FROM DirectReports
308 ORDER BY Sort;
309 GO
310
311 USE AdventureWorks;
312 GO
313 --Creates an infinite loop
314 WITH cte (EmployeeID, ManagerID, Title) as
315 (
316 SELECT EmployeeID, ManagerID, Title
317 FROM HumanResources.Employee
318 WHERE ManagerID IS NOT NULL
319 UNION ALL
320 SELECT cte.EmployeeID, cte.ManagerID, cte.Title
321 FROM cte
322 JOIN HumanResources.Employee AS e
323 ON cte.ManagerID = e.EmployeeID
324 )
325 --Uses MAXRECURSION to limit the recursive levels to 2
326 SELECT EmployeeID, ManagerID, Title
327 FROM cte
328 OPTION (MAXRECURSION 2);
329 GO
330
331
332
333 USE AdventureWorks;
334 GO
335 WITH cte (EmployeeID, ManagerID, Title)
336 AS
337 (
338 SELECT EmployeeID, ManagerID, Title
339 FROM HumanResources.Employee
340 WHERE ManagerID IS NOT NULL
341 UNION ALL
342 SELECT e.EmployeeID, e.ManagerID, e.Title
343 FROM HumanResources.Employee AS e
344 JOIN cte ON e.ManagerID = cte.EmployeeID
345 )
346 SELECT EmployeeID, ManagerID, Title
347 FROM cte;
348 GO
349
350
351 USE AdventureWorks;
352 GO
353 WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
354 (
355 SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
356 b.EndDate, 0 AS ComponentLevel
357 FROM Production.BillOfMaterials AS b
358 WHERE b.ProductAssemblyID = 800
359 AND b.EndDate IS NULL
360 UNION ALL
361 SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
362 bom.EndDate, ComponentLevel + 1
363 FROM Production.BillOfMaterials AS bom
364 INNER JOIN Parts AS p
365 ON bom.ProductAssemblyID = p.ComponentID
366 AND bom.EndDate IS NULL
367 )
368 SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
369 ComponentLevel
370 FROM Parts AS p
371 INNER JOIN Production.Product AS pr
372 ON p.ComponentID = pr.ProductID
373 ORDER BY ComponentLevel, AssemblyID, ComponentID;
374 GO
375
2 (
3 Id INT IDENTITY(1,1) PRIMARY KEY,
4 [Name] varchar(30) null,
5 JobTitle varchar(30) null,
6 Manager int null
7 )
8
9 insert Employee
10 select 'incf', 'IT Director',null union all
11 select 'inc3', 'Finance Director',null union all
12 select 'geovindu', 'assces',1 union all
13 select 'du', 'assces',1 union all
14 select 'fa','account',2 union all
15 select 'd','account',2
16
17 WITH OrganisationChart (Id, [Name], JobTitle, Manager) AS
18 (
19 SELECT
20 Id, [Name], JobTitle, Manager
21 FROM dbo.Employee
22 WHERE
23 Manager IS NULL
24 UNION ALL
25 SELECT emp.Id, emp.[Name], emp.JobTitle, emp.Manager
26 FROM dbo.Employee emp
27 INNER JOIN OrganisationChart ON
28 emp.Manager = OrganisationChart.Id
29 )
30 SELECT * FROM OrganisationChart
31
32
33 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager) AS
34 (
35 SELECT
36 Id, [Name], JobTitle, 0, Manager
37 FROM dbo.Employee
38 WHERE
39 Manager IS NULL
40 UNION ALL
41 SELECT emp.Id, emp.[Name], emp.JobTitle, [Level] + 1, emp.Manager
42 FROM dbo.Employee emp
43 INNER JOIN OrganisationChart ON
44 emp.Manager = OrganisationChart.Id
45 )
46 SELECT * FROM OrganisationChart
47 ORDER BY [Level]
48
49
50 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager, [Root]) AS
51 (
52 SELECT Id, [Name], JobTitle, 0, Manager, Id
53 FROM dbo.Employee
54 WHERE Manager IS NULL
55 UNION ALL
56 SELECT emp.Id,emp.[Name],emp.JobTitle,[Level] + 1,emp.Manager,[Root]
57 FROM dbo.Employee emp
58 INNER JOIN OrganisationChart ON
59 emp.Manager = OrganisationChart.Id
60 )
61 SELECT * FROM OrganisationChart
62 --WHERE [Name] = 'incf'
63 WHERE [Root] = 1
64
65
66
67 --示例数据库
68
69 /*
70 递归查询对于同一个表父子关系的计算提供了很大的方便，这个示例使用了SQL server 2005中的递归查询,使用的表是CarParts，这个表存储了一辆汽车的所有零件以及结构，part为零件单位，subpart为子零件，Qty为数量。
71
72 具体示例如下：
73
74 */
75 CREATE TABLE CarParts
76
77 (
78
79 CarID INT NOT NULL,
80
81 Part VARCHAR(15),
82
83 SubPart VARCHAR(15),
84
85 Qty INT
86
87 )
88
89 GO
90
91 INSERT CarParts VALUES (1, 'Body', 'Door', 4)
92
93 INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
94
95 INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
96
97 INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
98
99 INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
100
101 INSERT CarParts VALUES (1, 'Door', 'Window', 1)
102
103 INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
104
105 INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
106
107 INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
108
109 INSERT CarParts VALUES (1, 'Mirror', 'small_Mirror', 4)
110
111 GO
112
113 SELECT * FROM CarParts
114
115 GO
116
117 /*
118
119 一辆汽车需要各个零件的数目
120
121 1个Body 需要4个Door
122
123 1个Door 需要1个Mirror
124
125 那么
126
127 1个body需要4个Mirror
128
129 结构很简单吧
130
131 */
132
133 WITH CarPartsCTE(SubPart, Qty)
134
135 AS
136
137 (
138
139 -- 固定成员 (AM):
140
141 -- SELECT查询无需参考CarPartsCTE
142
143 -- 递归从此处开始
144
145 SELECT SubPart, Qty
146
147 FROM CarParts
148
149 WHERE Part = 'Body'
150
151 UNION ALL
152
153 -- 递归成员 (RM):
154
155 -- SELECT查询参考CarPartsCTE
156
157 -- 使用现有数据往下一层展开
158
159 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
160
161 FROM CarPartsCTE
162
163 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
164
165 WHERE CarParts.CarID = 1
166
167 )
168
169 SELECT SubPart,Qty AS TotalNUM
170
171 FROM CarPartsCTE
172
173 /*
174
175 注意看最下层的small_Mirror 位于 表最后的位置，
176
177 由此可以看出改递归不是开始就进行递归查询而是在1层完全展开后在根据该层展开下一层不是深度优先的递归
178
179 */
180
181 drop table CarParts
182
183
184
185 WITH DirectReports(groupid, member, EmployeeLevel,type) AS
186
187 (
188
189 SELECT groupid, member, 0,type AS EmployeeLevel
190
191 FROM groupinfo
192
193 WHERE groupid = 'finance_company'
194
195 UNION ALL
196
197 SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
198
199 FROM groupinfo e
200
201 INNER JOIN DirectReports d
202
203 ON e.groupid = d.member
204
205 )
206
207 SELECT b.nickname,groupid, member, EmployeeLevel,type
208
209 FROM DirectReports,userbasicinfo b
210
211 where DirectReports.member=b.id
212
213 and type = 1
214
215
216 USE AdventureWorks;
217 GO
218 WITH DirReps(ManagerID, DirectReports) AS
219 (
220 SELECT ManagerID, COUNT(*)
221 FROM HumanResources.Employee AS e
222 WHERE ManagerID IS NOT NULL
223 GROUP BY ManagerID
224 )
225 SELECT ManagerID, DirectReports
226 FROM DirReps
227 ORDER BY ManagerID;
228 GO
229
230
231 WITH DirReps (Manager, DirectReports) AS
232 (
233 SELECT ManagerID, COUNT(*) AS DirectReports
234 FROM HumanResources.Employee
235 GROUP BY ManagerID
236 )
237 SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
238 FROM DirReps
239 WHERE DirectReports>= 2 ;
240 GO
241
242
243
244
245 USE AdventureWorks;
246 GO
247 WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
248 AS
249 (
250 SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
251 FROM Sales.SalesOrderHeader
252 GROUP BY SalesPersonID
253 )
254 SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
255 E.ManagerID, OM.NumberOfOrders, OM.MaxDate
256 FROM HumanResources.Employee AS E
257 JOIN Sales_CTE AS OS
258 ON E.EmployeeID = OS.SalesPersonID
259 LEFT OUTER JOIN Sales_CTE AS OM
260 ON E.ManagerID = OM.SalesPersonID
261 ORDER BY E.EmployeeID;
262 GO
263
264 ---管理員下的下屬
265 USE AdventureWorks;
266 GO
267 WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
268 (
269 SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
270 FROM HumanResources.Employee
271 WHERE ManagerID IS NULL
272 UNION ALL
273 SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
274 FROM HumanResources.Employee e
275 INNER JOIN DirectReports d
276 ON e.ManagerID = d.EmployeeID
277 )
278 SELECT ManagerID, EmployeeID, EmployeeLevel
279 FROM DirectReports
280 WHERE EmployeeLevel <= 2 ;
281 GO
282
283 USE AdventureWorks;
284 GO
285 WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
286 AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
287 e.Title,
288 e.EmployeeID,
289 1,
290 CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
291 FROM HumanResources.Employee AS e
292 JOIN Person.Contact AS c ON e.ContactID = c.ContactID
293 WHERE e.ManagerID IS NULL
294 UNION ALL
295 SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
296 c.FirstName + ' ' + c.LastName),
297 e.Title,
298 e.EmployeeID,
299 EmployeeLevel + 1,
300 CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
301 LastName)
302 FROM HumanResources.Employee as e
303 JOIN Person.Contact AS c ON e.ContactID = c.ContactID
304 JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
305 )
306 SELECT EmployeeID, Name, Title, EmployeeLevel
307 FROM DirectReports
308 ORDER BY Sort;
309 GO
310
311 USE AdventureWorks;
312 GO
313 --Creates an infinite loop
314 WITH cte (EmployeeID, ManagerID, Title) as
315 (
316 SELECT EmployeeID, ManagerID, Title
317 FROM HumanResources.Employee
318 WHERE ManagerID IS NOT NULL
319 UNION ALL
320 SELECT cte.EmployeeID, cte.ManagerID, cte.Title
321 FROM cte
322 JOIN HumanResources.Employee AS e
323 ON cte.ManagerID = e.EmployeeID
324 )
325 --Uses MAXRECURSION to limit the recursive levels to 2
326 SELECT EmployeeID, ManagerID, Title
327 FROM cte
328 OPTION (MAXRECURSION 2);
329 GO
330
331
332
333 USE AdventureWorks;
334 GO
335 WITH cte (EmployeeID, ManagerID, Title)
336 AS
337 (
338 SELECT EmployeeID, ManagerID, Title
339 FROM HumanResources.Employee
340 WHERE ManagerID IS NOT NULL
341 UNION ALL
342 SELECT e.EmployeeID, e.ManagerID, e.Title
343 FROM HumanResources.Employee AS e
344 JOIN cte ON e.ManagerID = cte.EmployeeID
345 )
346 SELECT EmployeeID, ManagerID, Title
347 FROM cte;
348 GO
349
350
351 USE AdventureWorks;
352 GO
353 WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
354 (
355 SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
356 b.EndDate, 0 AS ComponentLevel
357 FROM Production.BillOfMaterials AS b
358 WHERE b.ProductAssemblyID = 800
359 AND b.EndDate IS NULL
360 UNION ALL
361 SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
362 bom.EndDate, ComponentLevel + 1
363 FROM Production.BillOfMaterials AS bom
364 INNER JOIN Parts AS p
365 ON bom.ProductAssemblyID = p.ComponentID
366 AND bom.EndDate IS NULL
367 )
368 SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
369 ComponentLevel
370 FROM Parts AS p
371 INNER JOIN Production.Product AS pr
372 ON p.ComponentID = pr.ProductID
373 ORDER BY ComponentLevel, AssemblyID, ComponentID;
374 GO
375