Oracle树形结构查询

from(
SELECT   se.test_set_id,ca.serial_id, se.test_set_name,se.task_name, se.build_tab, ca.test_case_no,ca.test_case_name, se.responsibility ,se.participant ,se.begin_date, se.end_date,se.test_environment_name,
ca.version_id,ca.is_latest, ca.ca_level, para1.param_text do_status, decode(instr(to_char(do.do_date, 'yyyy-mm-dd'),'1900-01-01'), 0,do.do_date,'') do_date, ca.serial_number,  decode(ca.estimate_test_do_time,-1,'',ca.estimate_test_do_time) estimate_test_do_time,decode(do.grand_total_do_time,0,'',do.grand_total_do_time) grand_total_do_time, do.test_do_desc,do.excuteuser
          FROM OT_TEST_DO do
          left join ot_parameter para1 on do.status = para1.param_value and para1.param_name = 'TestDoStatus'
          inner join  (select  se.serial_id test_set_id, se.test_set_name,pms.task_name, se.responsibility ,
                        se.participant ,se.begin_date , se.end_date ,
                        en.test_environment_name,
                        (select ta.build_tab
                          from (select f.serial_id, f.fold_name as build_tab, f.parent_id
                                from ot_test_folder f where f.enabled_flag = 1 and f.test_object_id = {#TEST_OBJECT_ID#} and f.org_id = {#ORG_ID#}
                              union
                              select t.serial_id, t.build_tab, -1 as parent_id
                                from OT_TEST_BUILD_TAB t where t.enabled_flag = 1 and t.test_object_id = {#TEST_OBJECT_ID#} and t.org_id = {#ORG_ID#}
                             ) ta
                         where ta.parent_id = -1
                         start with ta.serial_id = se.PARENT_OBJECT_ID
                         connect by ta.serial_id = prior ta.parent_id) as build_tab
                    from OT_TEST_SET se
                    left join
                         (select spr.test_set_id, wmsys.wm_concat(pmt.task_name) as task_name
                                  from ot_test_set_pms_relation spr
                                  inner join ot_pms_task pmt on spr.pms_task_id = pmt.serial_id
                                  where spr.test_object_id = {#TEST_OBJECT_ID#} and spr.enabled_flag = 1 and spr.org_id = {#ORG_ID#}
                                  and pmt.test_object_id = {#TEST_OBJECT_ID#} and pmt.enabled_flag = 1 and pmt.org_id = {#ORG_ID#}
{$ and pmt.task_name like {?30?} escape '/' $}
                                  group by spr.test_set_id) pms on se.serial_id = pms.test_set_id
                    left join ot_test_environment en on se.test_environment_id = en.serial_id
                    where
                    se.test_object_id = {#TEST_OBJECT_ID#}
                    and se.org_id = {#ORG_ID#}
                    and se.enabled_flag = 1
                    {$ and pms.task_name like {?30?} escape '/' $}
                    {$ and se.test_set_name like {?1?} escape '/' $}
                    {$ and se.responsibility like {?4?}escape '/' $}
                    {$ and se.participant like {?5?} escape '/'$}
                    {$ and se.begin_date >= {?6_s?} $} {$ and se.begin_date<= {?6_e?}  $}
                    {$ and se.end_date >= {?7_s?} $} {$ and se.end_date<= {?7_e?}  $}
                    {$ and en.serial_id = {?20?} $}
                  ) se on do.test_set_id = se.test_set_id {$ and se.build_tab like {?3?} escape '/' $}
           inner join (select cas.serial_id, cas.test_case_no,cas.test_case_name,cas.version_id,
                    case when cas.enabled_flag <> 1 then 'N/A'  when cas.is_latest = 0 then '否' else '是' end is_latest, para.param_text ca_level, cas.estimate_test_do_time,
                    (select wmsys.wm_concat(serial_number)
                        from OT_EC_CHANGE ec
                           where  ec.test_case_id = cas.serial_id
                    ) serial_number
                    from OT_TEST_CASE cas
                    left join ot_parameter para on cas.test_case_level = para.param_value  and para.param_name = 'TestCaseLevel'
                    where
                    cas.test_object_id = {#TEST_OBJECT_ID#}
                    and cas.org_id = {#ORG_ID#}
                    {$ and cas.test_case_no like {?10?} escape '/' $}
                    {$ and cas.test_case_name like {?11?} escape '/' $}
                    {$ and cas.test_case_level = {?22?} $}
                    ) ca on do.test_case_id = ca.serial_id
          
         WHERE
            do.ENABLED_FLAG = 1
           AND do.test_object_id = {#TEST_OBJECT_ID#}
           AND do.org_id = {#ORG_ID#}
           {$ and do.status = {?23?} $}
           {$ and do.do_date >= {?24_s?} $} {$ and do.do_date <= {?24_e?} +1 $}
           {$ and ca.serial_number like {?25?} escape '/' $}
)

http://cache.baidu.com/c?m=9d78d513d99e01fc09b3c3690d678b3b481fd13d6bc3975521dbc90ed5264c40347bfefe62670704a49e3a2546ff5e5c9dac61342a457af7cc9bde5dddcd912929822031740b863711c419d9c85125b67ad605b7&p=83759a46d0c41ce81abe9b7d074acc3e&newp=c23b8b1985cc42a518abc4710e10d0275c5bc4387ebad6157e94d5&user=baidu&fm=sc&query=oracle++nocycle&qid=&p1=1