MSSqlserver取层级数量

with temp_obs as
(
select obs_id,obs_name,parent_obs_id,1 as [level] from obs where enable_flag=1
and proj_id=’AAAAA’ AND parent_obs_id IS null
union all
select A.obs_id,A.obs_name,A.parent_obs_id,B.[level]+1 from obs A,temp_obs B where A.parent_obs_id=B.obs_id
AND enable_flag=1
and proj_id=’AAAAA’
)
select * from temp_obs