DECLARE @ID int
SET @ID=1
WITH CTETest as
(
select * from TestRecursive WHERE ParentID=@ID
union all
SELECT a.* FROM TestRecursive a inner join CTETest b
on a.ParentID=b.ID
)
select * from CTETest
Sql Server 2005+中用公用表表达式(CTE)实现递归查询
DECLARE @ID int
SET @ID=1
WITH CTETest as
(
select * from TestRecursive WHERE ParentID=@ID
union all
SELECT a.* FROM TestRecursive a inner join CTETest b
on a.ParentID=b.ID
)
select * from CTETest