整理oracle 树形查询
注:本文参考了《整理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) )
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、查找某个节点下面子孙节点
2、查找节点的祖先节点
![image image](https://images2018.cnblogs.com/blog/672567/201807/672567-20180719151136448-290429330.png)
在树形结构节点很多的情况一下,一般会采用异步刷新的方式进行,在默认加载的情况下,会展开到某个层级。这种情况下,不但要获取某个节点的祖先节点,还需要获取祖先节点的兄弟节点,在这种情况下可以通过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;
为人:谦逊、激情、博学、审问、慎思、明辨、 笃行
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处! ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处! ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/