是啊现在在表里面的结构是项目ID,父项目ID,都是有父子关系的, 现在假如我已经用一条SQL找到了一些叶子项目, 如何通过一条SQL语句找出所有拥有这些叶子的全部的树 我自己写了个SQL要70几秒。。就是先从下往上找到所有的根,再从上往下找到所有的结点。。要用两个connect by,大家有没有优化的方法啊 -------------------------SELECT LEVEL, TP.PROJECTID, TP.PARENTPROJECT FROM TXPROJECT TP START WITH TP.PROJECTID IN (SELECT T.PROJECTID FROM TXPROJECT T WHERE T.PARENTPROJECT = 0
START WITH T.PROJECTID IN (SELECT T.PROJECTID FROM V_PHASE_SIGN_C T) CONNECT BY PRIOR T.PARENTPROJECT = T.PROJECTID) CONNECT BY PRIOR TP.PROJECTID = TP.PARENTPROJECT
现在假如我已经用一条SQL找到了一些叶子项目,
如何通过一条SQL语句找出所有拥有这些叶子的全部的树 我自己写了个SQL要70几秒。。就是先从下往上找到所有的根,再从上往下找到所有的结点。。要用两个connect by,大家有没有优化的方法啊
-------------------------SELECT LEVEL, TP.PROJECTID, TP.PARENTPROJECT
FROM TXPROJECT TP
START WITH TP.PROJECTID IN
(SELECT T.PROJECTID
FROM TXPROJECT T
WHERE T.PARENTPROJECT = 0
START WITH T.PROJECTID IN
(SELECT T.PROJECTID FROM V_PHASE_SIGN_C T)
CONNECT BY PRIOR T.PARENTPROJECT = T.PROJECTID)
CONNECT BY PRIOR TP.PROJECTID = TP.PARENTPROJECT