SQL2005的cte递归查询子树

;with cte
as(
select id,caption,parentid,1 Gen from skywfflow where parentid =0
UNION ALL
select a.id,a.caption,a.parentid,cte.gen+1 Gen from skywfflow a
 inner join cte on a.parentid=cte.id
)
select * from cte