ORACLE——初用递归

 

 

 

 

SELECT ROWNUM,bmbaent,bmbasite,
      connect_by_root(bmaa001) root,
      bmba002,bmaa001,bmba003,
      LEVEL level1,
      sys_connect_by_path(LEVEL,'∠') level2
FROM bmaa_t 
INNER JOIN bmba_t ON bmbaent=bmaaent AND bmbasite=bmaasite AND bmba001=bmaa001 AND bmba002=bmaa002
START WITH bmaaent=90 AND bmaasite='D' AND bmaa001='3102010165' AND bmaa002=9 AND bmaastus='Y' 
CONNECT BY bmaa001 = PRIOR bmba003 AND bmba006 IS NULL AND bmbaent=90 AND bmbasite='D' AND bmba002=9 
ORDER BY ROWNUM

 

SELECT ROWNUM,bmbaent,bmbasite,
connect_by_root(bmaa001) root,
bmba002,bmaa001,bmba003,
LEVEL level1,
sys_connect_by_path(LEVEL,'∠') level2
FROM bmaa_t
INNER JOIN bmba_t ON bmbaent=bmaaent AND bmbasite=bmaasite AND bmba001=bmaa001 AND bmba002=bmaa002
START WITH bmaaent=90 AND bmaasite='D' AND bmaa001='3102010165' AND bmaa002=9 AND bmaastus='Y'
CONNECT BY bmaa001 = PRIOR bmba003
AND (bmba006 IS NULL OR to_number(to_char(bmba006,'yymmddhh24miss')) >= to_number(to_char(SYSDATE,'yymmddhh24miss')) )
AND bmbaent=90 AND bmbasite='D' AND bmba002=9
ORDER BY ROWNUM

 

学习参考资料：

https://www.cnblogs.com/benbenduo/p/4588612.html 

https://www.cnblogs.com/supermandy/p/7873233.html