oracle ORDER SIBLINGS BY

1.ORDER SIBLINGS BY     

与CONNECT BY PRIOR搭配使用，主要用于递归查询一颗树状组织结构时，能够将树本身的结构“原貌”展示出来（树状结构的根节点、枝节点、叶子节点呈现“树”状），定显示结果集时按树状在页面上的显示顺序展示数据。
例子如下：SELECT lpad(jdbt,lengthB(jdbt)+(LEVEL-1)*2),yyzy_id
  FROM SYS_AIS02
  WHERE sfxs='是'
  AND ML_ID=12000
 START WITH SJJD_ID IS NULL
CONNECT BY PRIOR JD_ID = SJJD_ID
 ORDER SIBLINGS BY SJJD_ID, JDXSSX;

 

2. 关于日期与order by 的一点小理解

oracle支持两种INTEVAL类型，它们分别是YEAR TO MONTH和DAY TO SECOND。
month后面跟地表示精度
-----interval year(2) to month
SQL> select interval'300'month(2) from dual;

INTERVAL'300'MONTH(2)
---------------------------------------------------------------------------
+25-00

------interval DAY(2) to second

SQL> select interval'20'second(2) from dual;

INTERVAL'20'SECOND(2)
---------------------------------------------------------------------------
+00 00:00:20.000000

SQL> select interval'1485462'second(2) from dual;

INTERVAL'1485462'SECOND(2)
---------------------------------------------------------------------------
+17 04:37:42.000000

SQL> select interval'10086'second(2) from dual;

INTERVAL'10086'SECOND(2)
---------------------------------------------------------------------------
+00 02:48:06.000000

 

至于order by的一点用法，是在看郭老师的每日一题时看到的，以前竟然一直没用过，惭愧啊。

SQL> select id,name from t1 order by name asc;

        ID NAME
---------- --------------------
         1 aaaa
         2 bbbb
         3

SQL> select id,name from t1 order by name desc;

        ID NAME
---------- --------------------
         3
         2 bbbb
         1 aaaa

SQL> select id,name from t1 order by name nulls first;

        ID NAME
---------- --------------------
         3
         1 aaaa
         2 bbbb

SQL> select id,name from t1 order by name nulls last;

        ID NAME
---------- --------------------
         1 aaaa
         2 bbbb
         3