SELECT DISTINCT CONNECT_BY_ROOT(SEG.SEGMID) CC
FROM ADDREGM SEG
WHERE EXISTS (SELECT 1
FROM ADDRSEGMEQP EQP
WHERE EQP.DELETESTATE = 0
AND EQP.RESTYPEID = 2530
AND SEG.SEGMID = EQP.ADDRSEGMID)
AND SEG.DELETESTATE=0
AND SEG.SEGMTYPE>=180007
START WITH SEG.SEGMTYPE=180007
CONNECT BY PRIOR SEG.SEGMID = SEG.PARENTSEGMID
1、ADDREGM 标准地址表 ADDRSEGMEQP 标准地址绑定设备表
2、查找SEGMTYPE大于180007节点SEGMID 如果查询到下级有一个RESTYPEID = 2530 的ADDRSEGMID 即该SEGMID为所需
3、瓶颈 oracle的 start with connect by prior 是中序遍历 遍历所有节点,时间代价太大