id moduleid parentid
-- -------- --------
1 1700 1000
2 1701 1700
3 1702 1700
4 1703 1700
5 1704 1703
6 1705 1703 各位大虾:
当查询条件为moduleid = 1705时候,我怎样才能查询出1705的父节点为1000?谢谢!!!
-- -------- --------
1 1700 1000
2 1701 1700
3 1702 1700
4 1703 1700
5 1704 1703
6 1705 1703 各位大虾:
当查询条件为moduleid = 1705时候,我怎样才能查询出1705的父节点为1000?谢谢!!!
from yourTable
start with moduleid = 1705
connect by prior parentid = moduleid ;
我已经试过了是不行的
select CONNECT_BY_ROOT(parentid)
from moduleconfig
start with moduleid = 1086
connect by prior parentid = moduleid
报错ORA-01436:用户数据connect by 循环
connect by nocycle prior emp.CORPID = emp.MGR_CORPID有循环要用nocycle
SELECT CONNECT_BY_ROOT(PARENTID)
FROM MODULECONFIG
START WITH MODULEID = 1086
CONNECT BY NOCYCLE PRIOR PARENTID = MODULEID;
with x as (select moduleid,parentid,level lv
from yourTable
start with moduleid = 1705
connect by prior parentid = moduleid)
select parentid from x where lv = (select max(lv) from x);
from yourTable
start with moduleid = 1705
connect by nocycle prior parentid = moduleid)
select parentid from x where lv = (select max(lv) from x);
2 from (select sys_connect_by_path(moduleid, ',') s, level l
3 from t
4 start with moduleid = 1705
5 Connect by prior parentid = moduleid)
6 where l = (select max(lev)
7 from (select sys_connect_by_path(moduleid, ','), level lev
8 from t
9 start with moduleid = 1705
10 Connect by prior parentid = moduleid));
SUBSTR(S,INSTR(S,',',1,L)+1)
--------------------------------------------------------------------------------
1700