【IT168技术文档】

　　下面的例子是在SQL Server中实现的一个递归过程：

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