sys_connect_by_path的运用例子

sys_connect_by_path的使用例子
SELECT  id                                                  event_id,
        cnt                                                 ggggance_reason_cnt,
        LTRIM(SYS_CONNECT_BY_PATH(display_name, '; '),'; ') gggance_reason_list
  FROM  (
        --within each id, assign a row number and get a total count
        SELECT  id,
                display_name,
                ROW_NUMBER() OVER (
                  PARTITION BY id
                  ORDER BY seq) num,
                COUNT(*) OVER (
                  PARTITION BY id) cnt
          FROM  (
                --Get distinct id and display name
                SELECT  er.event_id   id,
                        r.reason_name display_name,
                        r.sequence_num seq
                  FROM  xxxar_reason er,
                        xxxason r
                  WHERE r.reason_id = er.reason_id
                ) sub1
        ) sub2
  WHERE num = cnt
  START WITH num = 1
  CONNECT BY PRIOR num = num-1
        AND  PRIOR id = id