层次查询测试

层次查询测试

1、建测试表
CREATE TABLE tbl_test
(
  ID    NUMBER,
  NAME  VARCHAR2(10),
  pid   NUMBER DEFAULT 0
);
/*drop table tbl_test;*/

2、填充测试数据
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','111','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','222','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','333','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','444','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','555','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('6','666','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('9','999','2');
commit;

3、查看数据状态
select * from tbl_test;

select t.* from tbl_test t start with t.id=1 connect by prior t.id=t.pid;


select level,t.* from tbl_test t start with t.id=1 connect by prior t.id=t.pid;

start with 根记录条件
connect by 父记录行和子记录行之间的关系 ,层次查询中 ,条件表达式必须使用 prior 操作符来指定 父记录行


从子记录到父记录递归
select * from tbl_test start with id=9 connect by prior pid=id;

用作构成循环
select level from dual connect by level<10;