高分求SQL语句，在Oracle中。
表(Tab1)的数据如下:
Level Content
=============================
0 aaa
1 ddd
1 eee
2 qqq
1 ccc
3 rrr
0 uuu
1 yyy
1 ppp
3 eee
6 ppp
0 ttt
1 jjj
.... .....
现在我想通过SQL语句或者Function，可以把Tab1表的数据导入另外一张表Tab2,并且得到以下效果
表Tab2
main sub
===============================
aaa ddd
aaa eee
aaa qqq
aaa ccc
aaa rrr
uuu yyy
uuu ppp
uuu eee
uuu ppp (数据可以重复,该表没有PK)
ttt jjj
*********************************************
就是在Tab1中,将Level列为0的Content值取出跟下面的Content组织记录插入Tab2,直到出现下一个Level等于0。不知道这样描述清楚嘛，希望高手帮忙。
[解决办法]
用游标就可以很简单的实现吧.
[解决办法]
declare
i integer;
temp varchar2(20);
cursor kk is
select rownum,level,cotent from table1 ;
begin
i:=0;
for v in kk loop
if (i=0 and v.level <> 0) then --直到找到第一个level=0的记录
continue;
end if;
if v.level =0 then
i:=1;
temp:=v.cotent;
end if;
if (i=1 and v.level <> 0) then
insert into table2
select temp ,v.cotent from dual;
end if;
end loop;
end;
[解决办法]
--测试数据
create table tab1(clevel int,content varchar2(100));
create table tab2(main varchar2(100),sub varchar2(100));
insert into tab1
select 0, 'aaa ' from dual union all
select 1, 'ddd ' from dual union all
select 1, 'eee ' from dual union all
select 2, 'qqq ' from dual union all
select 1, 'ccc ' from dual union all
select 3, 'rrr ' from dual union all
select 0, 'uuu ' from dual union all
select 1, 'yyy ' from dual union all
select 1, 'ppp ' from dual union all
select 3, 'eee ' from dual union all
select 6, 'ppp ' from dual union all
select 0, 'ttt ' from dual union all
select 1, 'jjj ' from dual;
--执行
declare
fcontent varchar2(100);
r tab1%rowtype;
cursor c is select rownum,t.* from tab1 t;
begin
for r in c loop
dbms_output.put_line( r.clevel );
if r.clevel=0 then
fcontent:=r.content;
else
insert into tab2 select fcontent,r.content from dual;
end if;
end loop;
end;
--输出结果
select * from tab2
aaaddd
aaaeee
aaaqqq
aaaccc
aaarrr
uuuyyy
uuuppp
uuueee
uuuppp
tttjjj
[解决办法]
改个9能用的，
select substr(c1,2,instr(c1, '/ ',2)-2), content
from (select sys_connect_by_path(x.content, '/ ') c1, x.*
from (select rownum n, l, content from tab1) x
start with x.l = 0
connect by prior n = n - 1
and x.l <> 0)
where l <> 0
