Oravcle积累

select *,connect_by_isleaf LEAF,LEVEL LV from T_CASE where (DATE_BEGIN<=sysdate or DATE_BEGIN is null) and ACTUAL_IMPLEMENT_MAIN=? and exists (select f.case_id from t_bl_define_flow f where actual_case_id=f.case_id and f.phase='jj' and f.target='0') start with PID is null connect by prior ACTUAL_CASE_ID=PID and prior flag='1' and prior isshow='1' and prior version=version order siblings by NLSSORT(CASE_NAME, 'NLS_SORT=SCHINESE_PINYIN_M')<!--    LEVEL LV    LEVEL关键字定义一个字段LV,来显示树的层级,从1开始--><!--    exists()适合外表结果集小    in适合内外表结果集都很大--><!--    sysdate  获取当前时间--><!--    connect_by_isleaf  新增一个判断树叶的字段,0不是,1是    start with pid is null 初始记录    connect by prior a=b 上一条记录的a是这一条记录的父母,也就是b    order siblings by NLSSORT(字段,"策略")兄弟姐妹间的排序     NLSSORT为排序策略,有三种策略        NLSSORT(字段名,'NLS_SORT = SCHINESE_PINYIN_M')拼音        NLSSORT(字段名,'NLS_SORT = SCHINESE_STROKE_M')笔画        NLSSORT(字段名,'NLS_SORT = SCHINESE_RADICAL_M')部首-->