select EDPT_NBR,DEPT_NAME,level
from tablename
start with DEPT_NAME='yourneed'
connect by prior EDPT_NBR=PARENT_DEPT_NBR
这是查子的.
select EDPT_NBR,DEPT_NAME,level
from tablename
start with DEPT_NAME='yourneed'
connect by prior PARENT_DEPT_NBR=EDPT_NBR
这是查父的.
from tablename
start with DEPT_NAME='yourneed'
connect by prior EDPT_NBR=PARENT_DEPT_NBR
这是查子的.
select EDPT_NBR,DEPT_NAME,level
from tablename
start with DEPT_NAME='yourneed'
connect by prior PARENT_DEPT_NBR=EDPT_NBR
这是查父的.
-------- --------- --------------------
100088 df23
100099 100088 d2w3
100051 were
100037 100051 ghfdhr
100001 100037 fhtr5
100003 100051 fbf436 rows selectedSQL>
SQL> SELECT t.*,
2 LEVEL current_level,
3 (SELECT COUNT(*) - 1 FROM test t1 START WITH t1.ID = t.ID CONNECT BY t1.ID = PRIOR t1.parent_id) top_num,
4 (SELECT COUNT(DISTINCT LEVEL) - 1 FROM test t1 START WITH t1.ID = t.ID CONNECT BY t1.parent_id = PRIOR t1.ID) buttom_num
5 FROM test t
6 START WITH parent_id IS NULL
7 CONNECT BY parent_id = PRIOR ID
8 /ID PARENT_ID NAME CURRENT_LEVEL TOP_NUM BUTTOM_NUM
-------- --------- -------------------- ------------- ---------- ----------
100088 df23 1 0 1
100099 100088 d2w3 2 1 0
100051 were 1 0 2
100037 100051 ghfdhr 2 1 1
100001 100037 fhtr5 3 2 0
100003 100051 fbf43 2 1 06 rows selected
LEVEL current_level,
(SELECT COUNT(DISTINCT LEVEL) - 1 FROM test t1 START WITH t1.ID = t.ID CONNECT BY t1.ID = PRIOR t1.parent_id) top_num,
(SELECT COUNT(DISTINCT LEVEL) - 1 FROM test t1 START WITH t1.ID = t.ID CONNECT BY t1.parent_id = PRIOR t1.ID) buttom_num
FROM test t
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR ID
(SELECT sum(LEVEL)
FROM pearcs.departments2
WHERE dept_nbr IN (A.dept_nbr, B.dept_nbr)
START WITH parent_dept_nbr IS NULL
CONNECT BY parent_dept_nbr = PRIOR dept_nbr) AS dept,
2*
(SELECT MAX(X.L)
FROM (SELECT dept_nbr,LEVEL L
FROM pearcs.departments2
START WITH dept_name = A.dept_name
CONNECT BY dept_nbr = PRIOR parent_dept_nbr) X,
(SELECT dept_nbr,LEVEL L
FROM pearcs.departments2
START WITH dept_name = B.dept_name
CONNECT BY dept_nbr = PRIOR parent_dept_nbr) Y
WHERE X.dept_nbr = Y.dept_nbr) AS ROOT
FROM pearcs.departments2 A, pearcs.departments2 B;