with递归

 

适用于树状结构，关键在于ParentGuid和Guid

 

--方案一
with A as (
select Guid,ParentGuid,Name_CN,IsProduceBizEntity,1 as xh from dbo.BAS_OrgFrame where Guid='000000020129' and SealUser is null--5e2eb9f1-7e30-4245-a698-c1df88e5c274
union all
select b.Guid,b.ParentGuid,b.Name_CN,b.IsProduceBizEntity,a.xh+1 xh from A a inner join dbo.BAS_OrgFrame b on a.ParentGuid=b.Guid and b.SealUser is null
)
--select * from A
select top 1 A.Guid from A where IsProduceBizEntity=1 order by xh

--方案二
with A as (
select Guid,ParentGuid,Name_CN,IsProduceBizEntity, 1 as xh from dbo.BAS_OrgFrame where Guid='000000020129' and SealUser is null--5e2eb9f1-7e30-4245-a698-c1df88e5c274
union all
select b.Guid,b.ParentGuid,b.Name_CN,b.IsProduceBizEntity, a.xh+1 as xh from A a inner join dbo.BAS_OrgFrame b on a.ParentGuid=b.Guid and b.SealUser is null and b.IsProduceBizEntity = 1
)
select A.ParentGuid, * from A