sqlserver2005 递归查询

从下往上搜索：递归查询
with jdcpclCTE(zj_gch,gch,ilayer)
as
(
   select zj_gch,gch,ilayer=1 from utmis.dbo.jdcpcl where zj_gch>''
  and zj_gch='SB07334'
   union all
    select a.zj_gch,a.gch,ilayer=ilayer+1 from utmis.dbo.jdcpcl a
   inner join jdcpclCTE b   on a.zj_gch=b.gch
)

select * from jdcpclCTE