SQL Server 2005中的递归实现

SELECT   *   INTO  t  -- 载入测试数据
FROM
(
     SELECT   1   AS  ID, ' food '   AS  Product, 0   AS  ParentID  UNION   ALL
     SELECT   2 , ' sport ' , 0   UNION   ALL
     SELECT   3 , ' drink ' , 1   UNION   ALL
     SELECT   4 , ' ball ' , 2   UNION   ALL
     SELECT   5 , ' fruit ' , 1   UNION   ALL
     SELECT   6 , ' apple ' , 5   UNION   ALL
     SELECT   7 , ' banana ' , 5   UNION   ALL
     SELECT   8 , ' football ' , 4   UNION   ALL
     SELECT   9 , ' basketball ' , 4   UNION   ALL
     SELECT   10 , ' peisi ' , 3   UNION   ALL
     SELECT   11 , ' wohaha ' , 3
)  AS  a
GO
WITH  ShowTree(ID,Product,ORDERBY)  AS
(
     SELECT  ID,Product,ID  FROM  t  WHERE  ParentID = 0   -- 固定成员
    
     UNION   ALL
     SELECT  t.ID,t.Product,ORDERBY * 100 + t.ID  FROM  ShowTree  AS  s
     INNER   JOIN  t  ON  s.ID  =  t.ParentID
)
SELECT   *   FROM  ShowTree  ORDER   BY   CAST (ORDERBY  AS   VARCHAR ( 50 )) -- OPTION(MAXRECURSION 1)

DROP   TABLE  t