oracle connect by及函数及生成等间隔的时间序列的方法

一、connect by 及相关函数的使用

-------------------------------------------

--路径关系 sys_connect_by_path(parentunit_id, ', ')

--connect_by_isleaf:叶子

--connect_by_root:根节点

--level：梯队，层级

-------------------------------------------

select level,

       unit_id,

       sys_connect_by_path(parentunit_id,', ')PATH,

       connect_by_isleaf leaf,

       connect_by_root unit_id "根节点"

  from j1_dw.etl_meta_unit t

 where unit_idin

       ('151.02','152.02','155.02','156.02','157.02','158.02')

 startwith parentunit_id ='100.02'

connect bynocycleprior unit_id = parentunit_id;

 

     LEVEL           UNIT_IDPATH                                                LEAF    根节点

---------- ------------------ ------------------------------------------------     ----     -----

         1            151.02 ,100.02                                              0    151.02

         2            152.02 ,