SYS_CONNECT_BY_PATH & ORDER SIBLINGS BYORDER SIBLI

1.SYS_CONNECT_BY_PATH(column, char)

SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

SQL> create table heritance
  2  (
  3  parent number,
  4  child number
  5  );
表が作成されました。
SQL> begin
  2  for i in 1..10
  3  loop
  4   insert into heritance values(i,i+1);
  5  end loop;
  6  commit;
  7  end;
  8
  9  /
PL/SQLプロシージャが正常に完了しました。
SQL> select * from heritance
  2  ;
    PARENT      CHILD
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5          6
         6          7
         7          8
         8          9
         9         10
        10         11
10行が選択されました。
SQL> insert into heritance values(null,1);
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL>
SQL> select level,sys_connect_by_path(child,'/') path
  2  from heritance
  3  start with parent is null
  4  connect by prior child = parent;
     LEVEL PATH
---------- ------------------------------
         1 /1
         2 /1/2
         3 /1/2/3
         4 /1/2/3/4
         5 /1/2/3/4/5
         6 /1/2/3/4/5/6
         7 /1/2/3/4/5/6/7
         8 /1/2/3/4/5/6/7/8
         9 /1/2/3/4/5/6/7/8/9
        10 /1/2/3/4/5/6/7/8/9/10
        11 /1/2/3/4/5/6/7/8/9/10/11
11行が選択されました。

*************************

****************************

SQL> create table test

  2  (

  3    a        NUMBER not null,

  4    b       VARCHAR2(3)

  5  )

  6  ;

表が作成されました。

SQL> insert into test values(1,'lu');

1行が作成されました。

SQL> insert into test values(1,'ke');

1行が作成されました。

SQL> insert into test values(2,'jin');

1行が作成されました。

SQL> insert into test values(2,'san');

1行が作成されました。

SQL> commit;

SQL> col MAX(SYS_CONNECT_BY_PATH(B,'')) format a20;

SQL> set linesize 200;

SQL> l

  1  select a, max(SYS_CONNECT_BY_PATH(b, ' '))

  2    from (select a, b, ROW_NUMBER() OVER(PARTITION BY a ORDER BY a) ROWNUMBER

  3            from test)

  4   start with ROWNUMBER = 1

  5  CONNECT BY ROWNUMBER - 1 = PRIOR ROWNUMBER

  6              and  a = PRIOR a

  7   GROUP BY a

  8*

SQL> /

         A MAX(SYS_CONNECT_BY_P

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

         1  lu ke

         2  jin san

**************************************

2.ORDER SIBLINGS BY
在 Oracle 中，ORDER SIBLINGS BY 定义返回时同一父亲下各个兄弟之间的顺序。
SQL> select * from heritance;
    PARENT      CHILD
---------- ----------
         1          2
         2          3
         3          4
         4         16
         5          6
         6          7
         7          8
         8          9
         9         10
        10         11
                    1
         4         12
         4         16
         4         13
         4         15
         4         14
16行が選択されました。
SQL> l
  1  select level,parent,child,sys_connect_by_path(child,'/') path
  2   from heritance
  3   start with parent is null
  4   connect by prior child = parent
  5*  order siblings by child
SQL> /
     LEVEL     PARENT      CHILD PATH
---------- ---------- ---------- ------------------------------
         1                     1 /1
         2          1          2 /1/2
         3          2          3 /1/2/3
         4          3          4 /1/2/3/4
         5          4         12 /1/2/3/4/12
         5          4         13 /1/2/3/4/13
         5          4         14 /1/2/3/4/14
         5          4         15 /1/2/3/4/15
         5          4         16 /1/2/3/4/16
         5          4         16 /1/2/3/4/16
10行が選択されました。
SQL>

Oracle SQL精妙SQL语句讲解

http://blog.chinaunix.net/u2/81359/showart_2098489.html