小弟今天碰到1张表,里面的结构大致是这样的 bh jb 地址
350802 1 新罗
35080201 2 东城办事处
3508020101 3 东新
350802010101 4 一组
。。
我想把地址便跟为
bh jb 地址
350802 1 新罗
35080201 2 新罗东城办事处
3508020101 3 新罗东城办事处东新
350802010101 4 新罗东城办事处东新一组
..
请问该如何何写啊?
350802 1 新罗
35080201 2 东城办事处
3508020101 3 东新
350802010101 4 一组
。。
我想把地址便跟为
bh jb 地址
350802 1 新罗
35080201 2 新罗东城办事处
3508020101 3 新罗东城办事处东新
350802010101 4 新罗东城办事处东新一组
..
请问该如何何写啊?
select bh,jb,地址||lag(地址)over(order by jb)from dual;
select '350802' bh,1 jb, '新罗' dz from dual union all
select '35080201' bh,2 jb, '东城办事处' dz from dual union all
select '3508020101' bh,3 jb, '东新' dz from dual union all
select '350802010101' bh,4 jb, '一组' dz from dual )
SELECT bh, jb, REPLACE(sys_connect_by_path(dz, ','), ',')
FROM t
START WITH jb = 1
CONNECT BY PRIOR jb = jb - 1;
bh varchar2(20) primary key,
jb number,
address varchar2(1000)
);insert into test_change(bh,jb,address) values('350802',1,'新罗');
insert into test_change(bh,jb,address) values('35080201',2,'东城办事处');
insert into test_change(bh,jb,address) values('3508020101',3,'东新');
insert into test_change(bh,jb,address) values('350802010101',4,'一组');
commit;insert into test_change(bh,jb,address) values('101011',1,'地球');
insert into test_change(bh,jb,address) values('10101101',2,'中国');
insert into test_change(bh,jb,address) values('1010110101',3,'北京');
insert into test_change(bh,jb,address) values('101011010101',4,'海淀');
commit;select b.bh,b.jb,replace(sys_connect_by_path (b.address,' '),' ','') as address from
(
select a.jb as jb,
a.bh as bh,
a.address as address,
row_number() over(partition by substr(bh, 1, 6) order by jb) as rn
from test_change a
) b
start with b.rn = 1
connect by prior b.rn = b.rn - 1
and prior substr(b.bh, 1, 6) = substr(b.bh, 1, 6) ;
bh jb address
101011 1 地球
10101101 2 地球中国
1010110101 3 地球中国北京
101011010101 4 地球中国北京海淀
350802 1 新罗
35080201 2 新罗东城办事处
3508020101 3 新罗东城办事处东新
350802010101 4 新罗东城办事处东新一组
declare
cursor c1 is select * from dos where jb='2' ;
cur_jtxx c1%rowtype;
begin
open c1;
loop
fetch c1 into cur_jtxx;
exit when c1%notfound;
UPDATE dos SET dz=cur_jtxx.dz||dz
WHERE jb='3' and bh like cur_jtxx.bh+'%' ;
end loop;
close c1;
end;