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