SqlServer2005 中表的递归查询

/*
这个例子使用了sqlserver2005中的递归查询
递归查询对于同一个表父子关系的计算提供了很大的方面不得不说是T-sql的一大飞跃
使用的表是CarParts
这个表存储了一辆汽车的所有零件以及结构
part为零件单位
subpart为子零件
Qty为数量
*/
CREATE TABLE CarParts
(
CarID INT NOT NULL,
Part VARCHAR(15),
SubPart VARCHAR(15),
Qty INT
)
GO
INSERT CarParts VALUES (1,'Body','Door',4)
INSERT CarParts VALUES (1,'Trunk Lid',1)
INSERT CarParts VALUES (1,'Car Hood','Handle','Lock','Window','Rivets',1000)
INSERT CarParts VALUES (1,100)
INSERT CarParts VALUES (1,'Mirror','small_Mirror',4)
GO
SELECT * FROM CarParts
GO
/*
一辆汽车需要各个零件的数目
1个Body 需要4个Door
1个Door 需要1个Mirror
那么
1个body需要4个Mirror
结构很简单吧
*/
WITH CarPartsCTE(SubPart,Qty)
AS
(
-- 固定成员 (AM):
-- SELECT查询无需参考CarPartsCTE
-- 递归从此处开始
SELECT SubPart,Qty
FROM CarParts
WHERE Part = 'Body'
UNION ALL
-- 递归成员 (RM):
-- SELECT查询参考CarPartsCTE
-- 使用现有数据往下一层展开
SELECT CarParts.SubPart,CarPartsCTE.Qty * CarParts.Qty
FROM CarPartsCTE
INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
WHERE CarParts.CarID = 1
)
SELECT SubPart,Qty AS TotalNUM
FROM CarPartsCTE
/*
注意看最下层的small_Mirror 位于 表最后的位置，
由此可以看出改递归不是开始就进行递归查询而是在1层完全展开后在根据该层展开下一层不是深度优先的递归
*/
drop table CarParts

--------------------------------result---------------------------------------

CarID?????? Part??????????? SubPart???????? Qty
----------- --------------- --------------- -----------
1?????????? Body??????????? Door??????????? 4
1?????????? Body??????????? Trunk Lid?????? 1
1?????????? Body??????????? Car Hood??????? 1
1?????????? Door??????????? Handle????????? 1
1?????????? Door??????????? Lock??????????? 1
1?????????? Door??????????? Window????????? 1
1?????????? Body??????????? Rivets????????? 1000
1?????????? Door??????????? Rivets????????? 100
1?????????? Door??????????? Mirror????????? 1
1?????????? Mirror????????? small_Mirror??? 4

(10 row(s) affected)

SubPart???????? TotalNUM
--------------- -----------
Door??????????? 4
Trunk Lid?????? 1
Car Hood??????? 1
Rivets????????? 1000
Handle????????? 4
Lock??????????? 4
Window????????? 4
Rivets????????? 400
Mirror????????? 4
small_Mirror??? 16

(10 row(s) affected)

今天的实例-----------------------------

以下示例显示经理以及向经理报告的雇员的层次列表。

WITH DirectReports(groupid,member,EmployeeLevel,type) AS ( ??? SELECT groupid,type AS EmployeeLevel ??? FROM groupinfo ??? WHERE groupid = 'finance_company' ??? UNION ALL ??? SELECT e.groupid,e.member,EmployeeLevel + 1,e.type ??? FROM groupinfo e ??????? INNER JOIN DirectReports d ??????? ON e.groupid = d.member ) SELECT b.nickname,groupid,type FROM DirectReports,userbasicinfo b where DirectReports.member=b.id and type = 1

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!