;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
SQL2005的cte递归查询子树
原文：http://www.cnblogs.com/hz-blog/p/4897763.html
