sql server 2005 中的用WITH CTE中实现迭归的的应用

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 行受影响)
*/