整理oracle 树形查询

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

注：本文参考了《整理oracle 树形查询》

sql树形递归查询是数据库查询的一种特殊情形，也是组织结构、行政区划查询的一种最常用的的情形之一。下面对该种查询进行一些总结：

create table JD2_SUPERVISE_MATTER
(
  id                     NUMBER(10) not null,
  pid                    NUMBER(10),
  data_model_id          NUMBER(10),
  code                   VARCHAR2(30),
  name                   VARCHAR2(30),
  descript               VARCHAR2(50),
  flag_notic_budget_unit CHAR(1),
  flag_notic_busi_dept   CHAR(1),
  busi_dept_name         VARCHAR2(30),
  orderid                NUMBER(10)
)

alter table JD2_SUPERVISE_MATTER
  add constraint PK_JD2_SUPERVISE_MATTER primary key (ID)

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (542, 362, null, 'ZCJH', '支出计划', null, null, null, null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (543, 542, null, 'YKJH', '用款计划申请', null, null, null, null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (544, 542, null, 'YKJHHZ', '用款计划汇总审核', null, null, null, null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (481, 382, 361, 'ZJZFSP', '直接支付申请汇总审批', null, '0', '1', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (361, null, 302, 'YSBZ', '预算编制', null, '1', '0', '', null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (363, 361, 21, 'NCYS', '年初预算', null, '1', '1', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (362, null, 101, 'YSZX', '预算执行', null, '0', '1', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (381, 362, 61, 'SRZX', '收入执行', null, '0', '1', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (382, 362, 101, 'ZCZX', '支出执行', null, '0', '0', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (383, 382, null, 'ZFSP', '支付审批', null, '1', '0', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (384, 383, 281, 'ZJZF', '直接支付', null, '0', '1', null, null);

insert into JD2_SUPERVISE_MATTER (ID, PID, DATA_MODEL_ID, CODE, NAME, DESCRIPT, FLAG_NOTIC_BUDGET_UNIT, FLAG_NOTIC_BUSI_DEPT, BUSI_DEPT_NAME, ORDERID)
values (521, null, 221, 'YSJH', '预算计划', null, '0', '1', null, null);

1、查找某个节点下面子孙节点

select * from JD2_SUPERVISE_MATTER start with id=361 connect by prior id=pid

2、查找节点的祖先节点

select * from JD2_SUPERVISE_MATTER start with id=361 connect by prior pid=id

在树形结构节点很多的情况一下，一般会采用异步刷新的方式进行，在默认加载的情况下，会展开到某个层级。这种情况下，不但要获取某个节点的祖先节点，还需要获取祖先节点的兄弟节点，在这种情况下可以通过level进行

select id,pid,level from JD2_SUPERVISE_MATTER where level>1
connect by prior id=pid start with id=361      order by level

显示出树的级别查询

select id,rpad(' ',(level-1)*4)||name,code,level from JD2_SUPERVISE_MATTER
 connect by pid = prior id   start with pid is null;