Oracle函数sys_connect_by_path

正序:

select ltrim(sys_connect_by_path(org_abbreviation, '-'),'-')
  from (select org_abbreviation,rownum rn
          from (select coi.org_abbreviation
                  from comm_org_subjection cos
                  left join comm_org_information coi on cos.org_id =
                                                        coi.org_id
                 where cos.bsflag = '0'
                   and length(cos.org_subjection_id) >
                       (select length(tt.org_subjection_id)
                          from comm_org_subjection tt
                         where tt.org_id =
                               (select tt.code_afford_org_id
                                  from comm_org_subjection tt
                                 where tt.org_id = 'C1000000004640'))
                 start with cos.org_subjection_id =
                            (select cos1.org_subjection_id
                               from comm_org_subjection cos1
                              where cos1.org_id = 'C1000000002957'
                                and cos1.bsflag = '0')
                connect by prior cos.father_org_id = cos.org_subjection_id
                 order by cos.org_subjection_id))
 start with rn = 1
connect by prior rn = rn - 1;

 

反序:

select ltrim(sys_connect_by_path(org_abbreviation, '-'),'-')
                  from comm_org_subjection cos
                  left join comm_org_information coi on cos.org_id =
                                                        coi.org_id
                 where cos.bsflag = '0'
                   and length(cos.org_subjection_id) >
                       (select length(tt.org_subjection_id)
                          from comm_org_subjection tt
                         where tt.org_id =
                               (select tt.code_afford_org_id
                                  from comm_org_subjection tt
                                 where tt.org_id = 'C1000000004640'))
                 start with cos.org_subjection_id =
                            (select cos1.org_subjection_id
                               from comm_org_subjection cos1
                              where cos1.org_id = 'C1000000002957'
                                and cos1.bsflag = '0')
                connect by prior cos.father_org_id = cos.org_subjection_id
                 order by cos.org_subjection_id;