27,579
社区成员
发帖
与我相关
我的任务
分享
create table [ceshi]
(id int, parentid int, code nvarchar(50))
insert into [ceshi]
select 1,0,'a' union all
select 2,1,'b' union all
select 3,1,'c' union all
select 4,2,'d' union all
select 5,2,'e' union all
select 6,2,'f' union all
select 7,3,'g' union all
select 8,3,'h' union all
select 9,4,'i' union all
select 10,5,'j' union all
select 11,9,'k' union all
select 12,7,'l' union all
select 13,7,'m' union all
select 14,8,'n' union all
select 15,13,'o' union all
select 16,14,'p'
declare @x varchar(5)
select @x='o';
with t as
(select id,parentid,code,lv=0 from [ceshi] where code=@x
union all
select b.id,b.parentid,b.code,a.lv+1
from t a
inner join [ceshi] b on a.parentid=b.id)
select top 1 code
from t
order by lv desc
/*
code
--------------------------------------------------
a
(1 row(s) affected)
*/