sqlserver2005 递归查询

--递归查询:从上往下搜索
with playadminCTE(plaparentid,plaid,platask,ilayer)
as
(
select plaparentid,plaid,platask,ilayer=1 from playadmin where plapadid=325
and plaparentid is null and plaid=307
union all
select a.plaparentid,a.plaid,a.platask,ilayer+1
from playadmin a INNER join playadminCTE b on
a.plaparentid=b.plaid --and b.plaid=6295
)

--递归查询:从下往上搜索
with playadminCTE(plaparentid,plaid,platask,ilayer)
as
(
select plaparentid,plaid,platask,ilayer=1 from playadmin where plaparentid is not null and plaid=6296
union all
select a.plaparentid,a.plaid,a.platask,ilayer=ilayer+1
from playadmin a INNER join playadminCTE b on
b.plaparentid=a.plaid
)