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