这是邹建书的列子利用CTE recursion找部门下的所有部门.

use test;
go

CREATE TABLE Dept( id int primary key,
                   parent_id int,
                   Name nvarchar(20))
insert Dept
select 0,0,N'<全部>' union all
select 1,0,N'财务部' union all
select 2,0,N'行政部' union all
select 3,0,N'业务部' union all
select 4,0,N'财务部' union all
select 5,4,N'销售部' union all
select 6,4,N'MIS'    union all
select 7,6,N'UI'     union all
select 8,4,N'软件开发' union all
select 9,8,N'内部开发';
go
Declare @Dept_name nvarchar(20)
Set @Dept_name=N'MIS';
with
DEPTS AS
(select * from Dept
 where Name=@Dept_name
 UNION ALL
 select A.*
 from Dept A,DEPTS B
 where A.parent_id=B.id)

 select * from DEPTS
 go

/*

id parent_id name

6      4      MIS
7     6      UI

*/

当时想如果反向找上级了,修改

Declare @Dept_name nvarchar(20)
Set @Dept_name=N'MIS';
with
DEPTS AS
(select * from Dept
 where Name=@Dept_name
 UNION ALL
 select A.*
 from Dept A,DEPTS B
 where A.id=B.parent_id)

 select * from DEPTS
 go

发现有如下报错

消息 530，级别 16，状态 1，第 3 行
语句被终止。完成执行语句前已用完最大递归 100。

 看起来是个死循环,找找原因,原来是 0,0,N'<全部>' ,这条记录问题

删除记录

Delete from Dept where id =0

再运行,结果正常了

总结,注意recusive 当中一定要注意不能有子项和父项相等记录,不然很容易死循环,如有的话,惟有加入限定条件了

online 的双重递归

CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO