oracle 更新父级id,求SQL语句：把子结点的ID用父结点的ID替换解决思路

SQL code-------建立测试环境

create table Po(id int,pup int,content varchar(10))

insert into po 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,3,'f'

union all select 7,3,'g'

union all select 8,5,'h'

union all select 9,5,'j'

union all select 10,8,'k'

---创建函数

create function pol(@id int)

returns int

as

begin

declare @t table(id int,pup int,lev int)

declare @i int

set @i=1

insert into @t select id,pup,@i from po where id in (select pup from po where id=@id)

while(@@rowcount>0)

begin

set @i=@i+1

insert into @t select b.id,b.pup,@i from @t a,po b where a.pup=b.id and a.lev=@i-1

end

declare @return int

declare @cnt int

select @cnt=count(*) from @t

if(@cnt>=2)

begin

select top 1 @return=id from (select top 2 * from @t order by lev desc)a order by lev

end

else

begin

set @return=@id

end

return @return

end

------调用函数：

select dbo.pol(id),pup,content from po