May 30 2014

oracle SYS_CONNECT_BY_PATH start with connect by prior

create table tmp_nine
(
addressid varchar2(20),
addressname varchar2(60),
addresslevel number,
fatheraddress varchar2(20)
)

SELECT * FROM tmp_nine;
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘10600’, ‘郑州市’, 1, ‘0’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘21555’, ‘二七区’, 2, ‘10600’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘34230’, ‘京广中路’, 3, ‘21555’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘154575’, ‘铁道小区(45号)’, 4, ‘34230’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘4576’, ‘1栋’, 5, ‘154575’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘765354’, ‘2单元’, 6, ‘4576’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘50978′, ’25楼’, 7, ‘765354’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘22356’, ‘8号’, 8, ‘50978’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘34214’, ‘新郑市’, 2, ‘10600’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘4325’, ‘北京路’, 3, ‘34214’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘354’, ‘万科花园’, 4, ‘4325’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘65636’, ‘5栋’, 5, ‘354’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘455456’, ‘1单元’, 6, ‘65636’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘4354’, ‘1楼’, 7, ‘455456’);
insert into TMP_NINE (addressid, addressname, addresslevel, fatheraddress)
values (‘455’, ‘2号’, 8, ‘4354’);
commit;
SELECT
     SYS_CONNECT_BY_PATH(a.addressid, ‘/’) as id_path,
     SYS_CONNECT_BY_PATH(a.addressname, ‘/’) as parent_name
                   from tmp_nine a
                  start with a.addressid = 10600
                 connect by prior a.addressid = a.fatheraddress       
SELECT
*
                   from tmp_nine a
                  start with a.addressid = 10600
                 connect by prior a.addressid = a.fatheraddress
        
         ORDER BY fatheraddress

 

 

 

———————

CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘1′,’10’,’0′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘2′,’11’,’1′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘3′,’20’,’0′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘4′,’12’,’1′);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(‘5′,’121′,’2’);

select * from TBL_TEST

select * from TBL_TEST
 start with id=2
 connect by prior id = pid



Copyright 2019. All rights reserved.

Posted 2014年5月30日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *