求SQL语句：把子结点的ID用父结点的ID替换
表数据如下：
IDPUPCONTENT
10a
21b
31c
42d
52e
63f
73g
85h
95j
108k
我希望得到2级结构，就是除了1，2级节点外，其它的节点的ID全部换成它的第二顶级节点ID,希望得到的结果如下：
IDPUPCONTENT
10a
21b
31c
22d
22e
33f
33g
25h
25j
28k
请高手帮忙
[解决办法]
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