SQL Server 2005 Hierarchies WITH Common Table Expressions

  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