sql server 2005 with如何实现递归（转自SOSO问问，作为记录），个人补充了其他

比如有张表,结构如下:

Example(ID int,ParentID int,PATH varchar)

取其某个ID为父节点的树结构的SQL如下：

WITH Example_Table(ID,ParentID)

AS

(

        --取根节点放入临时表

        SELECT ID,ParentID FROM Example WHERE ID = @ID PATH=CAST(ROW_NUMBER() OVER(ORDER BY ID) AS VARBINARY(MAX))

        --根据已取到的数据递归取其字节点的数据

        UNION ALL

        SELECT ID,ParentID FROM Example A INNER JOIN Example_Table B ON A.ParentID = B.ID CAST(B.PATH + CAST(ROW_NUMBER() OVER(PARTITION BY A.ParentID ORDER BY A.ID)
                         AS BINARY(4)) AS VARBINARY(MAX))

)

SELECT * FROM Example_Table order by PATH

该例子简单实用，一目了然，自己加入了改进