Organize Oracle Tree queries

Note: This article refers to the collation of Oracle tree query

SQL Tree recursive query is a special case of database query, and it is also one of the most common cases of organization structure and administrative division query. Here are some summary of the query:

CREATE TABLE Jd2_supervise_matter (  ID number                     () NOT NULL,  PID number                    (Ten),  data_model_id          Number (ten),  code                   VARCHAR2 (+),  name                   VARCHAR2 (+),  descript               VARCHAR2,  flag_ Notic_budget_unit char (1),  flag_notic_busi_dept   char (1),  busi_dept_name         VARCHAR2 (+),  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‘, ‘spending Plan', NULL, NULL, NULL, NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_noti C_budget_unit, Flag_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (543, 542, NULL, 'YKJH‘, ‘apply for a payment plan', NULL, NULL, NULL, NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_noti C_budget_unit, Flag_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (544, 542, NULL, 'ykjhhz‘, ‘Use Plan summary audit', NULL, NULL, NULL, NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_noti C_budget_unit, Flag_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (481, 382, 361, 'ZJZFSP‘, ‘Direct Payment Application Summary approval', NULL, '0‘, ‘1', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (361, NULL, 302, 'YSBZ‘, ‘budget Preparation', NULL, '1‘, ‘0‘, ‘', null); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, Flag_not Ic_busi_dept, Busi_dept_name, ORDERID) VALUES (363, 361, 21, 'Ncys‘, ‘Early Budget', NULL, '1‘, ‘1', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (362, NULL, 101, 'YSZX‘, ‘Budget Execution', NULL, '0‘, ‘1', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (381, 362, 61, 'SRZX‘, ‘Revenue Execution', NULL, '0‘, ‘1', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (382, 362, 101, 'ZCZX‘, ‘expenditure Execution', NULL, '0‘, ‘0', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (383, 382, NULL, 'ZFSP‘, ‘Payment Approval', NULL, '1‘, ‘0', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (384, 383, 281, 'Zjzf‘, ‘Direct Payment', NULL, '0‘, ‘1', NULL, NULL); INSERT into Jd2_supervise_matter (ID, PID, data_model_id, CODE, NAME, Descript, Flag_notic_budget_unit, FLA G_notic_busi_dept, Busi_dept_name, ORDERID) VALUES (521, NULL, 221, 'YSJH‘, ‘budget Plan', NULL, '0‘, ‘1', NULL, NULL);

1. Find descendants under a node

SELECT * from Jd2_supervise_matter start with id=361 connect by prior Id=pid

2. Find the ancestor node of the node

SELECT * from Jd2_supervise_matter start with id=361 connect by prior Pid=id

in the case of tree-structure nodes, it is usually done asynchronously, and in the case of default loading, it expands to a certain level. In this case, not only to get the ancestor node of a node, but also to get the ancestor nodes of the sibling node, in this case, the level can be

Select Id,pid,level from Jd2_supervise_matter where Level>1connect by prior id=pid start with id=361      order by level

Displays the level query for the tree

Select Id,rpad (", (level-1)) | | Name,code,level from Jd2_supervise_matter connect by PID = Prior ID   start with PID is null;

Organize Oracle Tree queries