Oracle：查询树形结构数据
建表：
|
1 2 3 4 5 6 |
create table tree_table( id number, name varchar2(40), base_id number, description varchar2(50) ); |
表结构：
|
1 2 3 4 5 6 7 8 9 |
SQL> desc tree_table; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(40) BASE_ID NUMBER DESCRIPTION VARCHAR2(50) SQL> |
插入数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
insert into tree_table(id,name) values (0,'Natural'); insert into tree_table values (1,'God',0,'who build the world.'); insert into tree_table values (2,'Devil',0,'who destory the world.'); insert into tree_table values (3,'Sofia',1,'First Saint.'); insert into tree_table values (4,'Human Being',0,null); insert into tree_table values (5,'Adam',4,null); insert into tree_table values (6,'Norval',4,null); insert into tree_table values (7,'Lucifer',2,null); insert into tree_table values (8,'War',2,null); insert into tree_table values (9,'Death',2,null); insert into tree_table values (10,'Weak',2,null); commit; |
查询：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> select name,base_id,id from tree_table start with id=0 connect by nocycle prior id=base_id order by id; NAME BASE_ID ID ---------------------------------------- ---------- ---------- Natural 0 God 0 1 Devil 0 2 Sofia 1 3 Human Being 0 4 Adam 4 5 Norval 4 6 Lucifer 2 7 War 2 8 Death 2 9 Weak 2 10 11 rows selected. SQL> |
树型显示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> col "Tree data" for a14 SQL> select 2 rpad(' ',(LEVEL-1)*2,' ') || name "Tree data" 3 from tree_table 4 start with id = 0 5 connect by prior id = base_id; Tree data -------------- Natural God Sofia Devil Lucifer War Death Weak Human Being Adam Norval 11 rows selected. SQL> |
————————————
Done。
