Have any oracle built-in function or better way,
Is more tree public ancestors LCA problem recently

CodePudding user response:

Connect by

CodePudding user response:

Demand looks something like this:

A tree in your organization, the user to monitor department, can be customized for any check node in the tree,

Events list shows all the regulatory department events, conditions can search area according to the regulatory department drop-down filter search tree;
Departments under the tree, not regulatory department gray display and choose, regulatory department of black display can choose;
Organization hierarchy tree may be too much, for the sake of the regulation only at the bottom of the unit, can not have one layer, from the root node
Need only to show his regulators in recent common ancestor, starting from the recent common ancestor, is beneficial to the user experience,

CodePudding user response:

Connect by all the ancestor nodes can look for a node recursive

Multiple nodes is known, is also to press with the union level sort to sort out first; For example:

 
Select id, LVL from (
The select, Anderson d a. vl from 
(SELECT ID, level as LVL from t_sys_organization l start with l.i d (200001042) in CONNECT BY ID=the PRIOR PID) a, 
(SELECT ID, level as LVL from t_sys_organization l start with l.i d (200001140) in CONNECT BY ID=the PRIOR PID) b 
Where Anderson, d=b.i d order by a. vl 
) where rownum=1 

But if there are multiple nodes is known, such as 100 known node, you need to do to 100 times the union, and the number of known nodes not sure, feel this is not the optimal solution;

CodePudding user response:

SELECT ID, level as LVL from t_sys_organization l start with l.i d in (200001042) in 100 nodes here,
Select id
The from (
Select id, minlvl row_number () over (order by minlvl) rn
The from (
Select id, min minlvl (LVL)
The from (
SELECT ID, level as LVL from t_sys_organization l start with l.i d (200001042100101) in CONNECT BY ID=the PRIOR PID
)
Group by id
Having the count (*)=100
))
Where an rn=1