树性结构实例

建表：

create table address(

  id  number(10) not null,

  name varchar2(256) not null,

  father_id number(10)

);

建立索引

create unique index address_1 on address(id) ;

创建主键

alter table address add constraint address_pk primary key(id) using index address_1;

创建外键

alter table address add constraint address_fk foreign key (father_id) references address(id);

初始化数据

insert into address values(1,'上海',null);

insert into address values(2,'虹口区',1);

insert into address values(3,'徐汇区',1);

insert into address values(4,'逸仙路',2);

insert into address values(5,'浦东新区',1);

insert into address values(6,'闸北区',1);

 

insert into address values(7,'安徽',null);

insert into address values(8,'合肥市',7);

insert into address values(9,'亳州市',7);

insert into address values(10,'涡阳县',9);

commit;

 

查询路径：(父亲--孩子)

     select 

  substr(SYS_CONNECT_BY_PATH(addr.name, '-'),2) Path

  from address addr

  start with addr.father_id is null

  connect by addr.father_id = prior addr.id;

  结果

上海

上海-虹口区

上海-虹口区-逸仙路

上海-徐汇区

上海-浦东新区

上海-闸北区

安徽

安徽-合肥市

安徽-亳州市

安徽-亳州市-涡阳县

 

查询路径：（孩子--父亲）

select 

  substr(SYS_CONNECT_BY_PATH(addr.name, '-'),2) Path

  from address addr

  start with addr.id = 10

  connect by addr.id = prior addr.father_id;

  结果

涡阳县

涡阳县-亳州市

涡阳县-亳州市-安徽