参考http://www.cnblogs.com/shenr/archive/2008/09/30/1323452.html
In Oracle, there is one way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is(same concept as we do laster in Ms Sql server 2008):
create table hierarchic_tbl (
id number primary key,
parent_id references hierarchic_tbl, -- references itself
descr varchar2(20),
active number(1) not null check (active in (0,1))
);
This table is now filled with some values:
-- 1st level:
insert into hierarchic_tbl values ( 1, null, 'Language' , 1);
-- 2nd level:
insert into hierarchic_tbl values ( 2, 1, 'VB' , 1);
insert into hierarchic_tbl values ( 3, 1, 'C#' , 1);
insert into hierarchic_tbl values ( 4, 1, 'JAVA', 1);
-- 3rd level (below VB)
insert into hierarchic_tbl values ( 5, 2, 'VB6.0' , 1);
insert into hierarchic_tbl values ( 6, 2, 'VB.NET' , 1);
-- 4th level (below VB.NET)
insert into hierarchic_tbl values (7, 6, 'VB.NET2003' , 1);
insert into hierarchic_tbl values (8, 6, 'VB.NET2005' , 1);
Showing the tree with start with .. connect by:
select
lpad(' ', (level-1)*2, ' ') || descr
from
hierarchic_tbl
start with parent_id is null
connect by prior id = parent_id;
------------------------------------------------
Result:
Language
VB
VB6.0
VB.NET
VB.NET2003
VB.NET2005
C#
Java
Mysql
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
In Oracle, there is one way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is(same concept as we do laster in Ms Sql server 2008):
create table hierarchic_tbl (
id number primary key,
parent_id references hierarchic_tbl, -- references itself
descr varchar2(20),
active number(1) not null check (active in (0,1))
);
This table is now filled with some values:
-- 1st level:
insert into hierarchic_tbl values ( 1, null, 'Language' , 1);
-- 2nd level:
insert into hierarchic_tbl values ( 2, 1, 'VB' , 1);
insert into hierarchic_tbl values ( 3, 1, 'C#' , 1);
insert into hierarchic_tbl values ( 4, 1, 'JAVA', 1);
-- 3rd level (below VB)
insert into hierarchic_tbl values ( 5, 2, 'VB6.0' , 1);
insert into hierarchic_tbl values ( 6, 2, 'VB.NET' , 1);
-- 4th level (below VB.NET)
insert into hierarchic_tbl values (7, 6, 'VB.NET2003' , 1);
insert into hierarchic_tbl values (8, 6, 'VB.NET2005' , 1);
Showing the tree with start with .. connect by:
select
lpad(' ', (level-1)*2, ' ') || descr
from
hierarchic_tbl
start with parent_id is null
connect by prior id = parent_id;
------------------------------------------------
Result:
Language
VB
VB6.0
VB.NET
VB.NET2003
VB.NET2005
C#
Java
Mysql
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html