一张部门表 现在知道一id 要通过id递归查询到这个部门的父级的父级 用了sql sever的递归方法 但是只查出了这个id的上级 求大神帮忙看下
with cte ( id,territoryparentid,level)
as
(
select Id, territoryparentid,3 as level
from t_s_territory
where id='130102'
union all
select a.Id, a.territoryparentid,cte.level-1 as level
from t_s_territory a
inner join cte on a.territoryparentid = cte.id
), cte1 ( id1,territoryparentid1,level) as(
select Id, territoryparentid,3 as level
from cte
--where territorylevel=1
union all
select b.Id, b.territoryparentid,cte1.level-2 as level
from cte b
inner join cte1 on b.territoryparentid = cte1.id1
)
select * from cte1