sql server 2000 中要实现迭归需要用函数处理.sql 2005提供了相应的迭归方法.
示例如下:
declare @t table( id int identity(1,1),name varchar(10),ParentID varchar(10))
insert into @t select 'a',''
union all select 'b',''
union all select 'a1',1
union all select 'a2',1
union all select 'b1',2
union all select 'a11',3
union all select 'a12',3
union all select 'b11',5
union all select 'b12',5
union all select 'b13',5
union all select 'a111',6
union all select 'a112',6
union all select 'a113',6
--1d=5,6 的情况
DECLARE @id AS varchar(200)
SET @id='5,6';
WITH TCTE(ID,name,ParentID,LVL)
AS
(
SELECT ID,name,ParentID,0
FROM @t WHERE charindex(','+ltrim(id)+',',','+@id+',')>0
UNION ALL
SELECT ta.ID,ta.name,ta.ParentID,TB.LVL+1
FROM @t TA INNER JOIN TCTE TB
ON TA.ParentID=TB.id
),
T_CTE(ID,name,ParentID,LVL)
AS
(
SELECT ID,name,ParentID,0
FROM @t WHERE charindex(','+ltrim(id)+',',','+@id+',')>0
UNION ALL
SELECT ta.ID,ta.name,ta.ParentID,TB.LVL+1
FROM @t TA INNER JOIN T_CTE TB
ON TA.id=TB.ParentID
)
SELECT ID,name,ParentID=case when ParentID=0 then null else ParentID end
FROM TCTE
UNION
SELECT ID,name,ParentID=case when ParentID=0 then null else ParentID end
FROM T_CTE


--结果

/**//*
ID name ParentID
----------- ---------- ----------
1 a NULL
2 b NULL
3 a1 1
5 b1 2
6 a11 3
8 b11 5
9 b12 5
10 b13 5
11 a111 6
12 a112 6
13 a113 6

(11 行受影响)
*/