表数据如下：
ID PUP CONTENT
1 0 a
2 1 b
3 1 c
4 2 d
5 2 e
6 3 f
7 3 g
8 5 h
9 5 j
10 8 k
我希望得到2级结构，就是除了1，2级节点外，其它的节点的ID全部换成它的第二顶级节点ID,希望得到的结果如下：
ID PUP CONTENT
1 0 a
2 1 b
3 1 c
2 2 d
2 2 e
3 3 f
3 3 g
2 5 h
2 5 j
2 8 k
请高手帮忙
------解决方案--------------------
select
level,
sys_connect_by_path(a,','),
decode(level,1,b,2,b,substr(sys_connect_by_path(a,','),instr(sys_connect_by_path(a,','),',',2)+1,1)),
t.*
from
(select 1 a,0 b,'a' from dual
union
select 2 a, 1 b, 'b' from dual
union
select 3 a, 1 b, 'c' from dual
union
select 4 a, 2 b, 'd' from dual
union
select 5 a, 2 b, 'e' from dual
union
select 6 a, 3 b, 'f' from dual
union
select 7 a, 3 b, 'g' from dual
union
select 8 a, 5 b, 'h' from dual
union
select 9 a, 5 b, 'j' from dual
union
select 10 a, 8 b, 'k' from dual ) t
connect by prior t.a = t.b
start with t.b=0
------解决方案--------------------
- 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 intasbegin 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 @returnend------调用函数：select dbo.pol(id),pup,content from po