2,497
社区成员
发帖
与我相关
我的任务
分享
HH FCHILDREN FCF1
---------- --------- ----
1 A aaaa
2 B bbbb
3 C cccc
HH FCHILDREN FCF1
---------- --------- ----
1 A aaaa
2 D dddd
3 B bbbb
4 C cccc
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as cvbom
SQL>
SQL> create table tb_dat as select * from (select '/' fparents,'A' fchildren,'aaaa' fcf1 from dual union
2 select 'A' fparents,'B' fchildren,'aaaa' fcf1 from dual union
3 select 'B' fparents,'C' fchildren,'aaaa' fcf1 from dual);
Table created
SQL> --类似链表 A - B - C
SQL> select * from tb_dat;
FPARENTS FCHILDREN FCF1
-------- --------- ----
/ A aaaa
A B aaaa
B C aaaa
SQL>
SQL> select level hh, fchildren, fcf1
2 from tb_dat
3 start with fparents = '/'
4 connect by fparents = prior fchildren;
HH FCHILDREN FCF1
---------- --------- ----
1 A aaaa
2 B aaaa
3 C aaaa
SQL> --查询出用户想要的顺序结果
SQL>
SQL> insert into tb_dat (fparents,fchildren,fcf1) values ('A','D','dddd');--增加A的子女D
1 row inserted
SQL> update tb_dat set fparents = 'D' where fchildren = 'B';--将B的双亲改为A
1 row updated
SQL> --以上2步操作类似,链表中增加元素D,并将D链接到A,断开A与B链接,重行将B链接到D
SQL> --形成 A - D - B - C
SQL> select * from tb_dat;
FPARENTS FCHILDREN FCF1
-------- --------- ----
/ A aaaa
D B aaaa
B C aaaa
A D dddd
SQL>
SQL> select level hh, fchildren, fcf1
2 from tb_dat
3 start with fparents = '/'
4 connect by fparents = prior fchildren;
HH FCHILDREN FCF1
---------- --------- ----
1 A aaaa
2 D dddd
3 B aaaa
4 C aaaa
SQL> --查询出用户想要的顺序结果
SQL>