oracle 层级查询

oracle 递归查询

• 简介

start with … connect by prior 一般用于层级查询具有树形结构的表

• 语法

select ...  from table_name 
    where condition
    start with condition
    connect by prior 

---

实际应用

-- 测试表&数据准备

create table hierarchical(
       region_id number not null,
       region_name varchar2(124),
       parent_region_id number,
       parent_region_name varchar2(124)
);
-- 备注
comment on table hierarchical is '创建区域表,以河南省部分地市,区县为例';
comment on column hierarchical.region_id is '区域id';
comment on column hierarchical.region_name is '区域名称';
comment on column hierarchical.parent_region_id is '上级区域id';
comment on column hierarchical.parent_region_name is '上级区域名称';

-- 添加主外键约束
alter table hierarchical
      add constraint hie_pk primary key (region_id);
alter table hierarchical 
      add constraint hie_hie_fk foreign key (parent_region_id) references hierarchical(region_id);
-- 新建索引
create index hie_parent_region_id_idx on hierarchical(parent_region_id);

-- 插入测试数据
insert into hierarchical values('1','河南省',null,null);

insert into hierarchical values('2','郑州','1','河南省');

insert into hierarchical values('3','郑州市','2','郑州');
insert into hierarchical values('4','新郑市','2','郑州');

insert into hierarchical values('5','金水区','3','郑州市');
insert into hierarchical values('6','二七区','3','郑州市');

insert into hierarchical values('7','新郑龙王','4','新郑市');
insert into hierarchical values('8','新郑八千','4','新郑市');

• 自顶向下查询

select h.region_name , '属于' 属于 , h.parent_region_name from hierarchical h
       where level = 1 
       -- where 的位置必须要在层级查询子句的前面
       start with h.parent_region_id is null 
       -- 限制root位置
       connect by prior h.region_id = h.parent_region_id;
       -- 表示开始位置的region_id 要等于 下一条记录的 parent_reggion_id
       -- 也可以直接查询 level 关键字 ,从而确定记录在当前查询树种的位置

• 自顶向下查询结果

  region_name	属于	parent_region_name
  河南省	属于
  郑州	属于	河南省
  郑州市	属于	郑州
  金水区	属于	郑州市
  二七区	属于	郑州市
  新郑市	属于	郑州
  新郑龙王	属于	新郑市
  新郑八千	属于	新郑市

• 自底向上查询

select *
       from hierarchical hi 
       start with hi.region_id = (select max(hc.region_id)  from hierarchical hc )
       connect by prior hi.parent_region_id = hi.region_id;

• 自底向上查询结果

region_id	region_name	parent_region_id	parent_region_name
8	新郑八千	4	新郑市
4	新郑市	2	郑州
2	郑州	1	河南省
1	河南省