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, 'Body', 'Trunk Lid', 1)
INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
INSERT CarParts VALUES (1, 'Door', 'Window', 1)
INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
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, member, 0,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, member, EmployeeLevel,type
FROM DirectReports,userbasicinfo b
where DirectReports.member=b.id
and type = 1