Oracle高级查询

使用Oracle特有的查询语法, 可以达到事半功倍的效果

1. 树查询

create   table  tree (
    id  number ( 10 )  not   null   primary   key ,
    name  varchar2 ( 100 )  not   null ,
    super  number ( 10 )  not   null                  //   0   is  root
);
--  从子到父
select   *   from  tree start  with  id  =  ? connect  by  id  =  prior super 
--  从父到子
select   *   from  tree start  with  id  =  ? connect  by  prior id  =  suepr
--  整棵树
select   *   from  tree start  with  super  =   0  connect  by  prior id  =  suepr

2. 分页查询

select   *   from  ( 
     select  my_table. * , rownum  my_rownum  from  ( 
         select  name, birthday  from  employee  order   by  birthday
    ) my_table  where  rownum  <   120  
)  where  my_rownum  >=   100 ;

3. 累加查询, 以scott.emp为例

select  empno, ename, sal,  sum (sal)  over ( order   by  empno) result  from  emp;
 
     EMPNO ENAME             SAL     RESULT
-- -------- ---------- ---------- ----------
       7369  SMITH              800          800
       7499  ALLEN             1600         2400
       7521  WARD              1250         3650
       7566  JONES             2975         6625
       7654  MARTIN            1250         7875
       7698  BLAKE             2850        10725
       7782  CLARK             2450        13175
       7788  SCOTT             3000        16175
       7839  KING              5000        21175
       7844  TURNER            1500        22675
       7876  ADAMS             1100        23775
       7900  JAMES              950        24725
       7902  FORD              3000        27725
       7934  MILLER            1300        29025

4. 高级group by

select  decode( grouping (deptno), 1 , ' all deptno ' ,deptno) deptno,
       decode( grouping (job), 1 , ' all job ' ,job) job,
        sum (sal) sal
from  emp 
group   by  ROLLUP(deptno,job);
DEPTNO                                   JOB              SAL
-- -------------------------------------- --------- ----------
10                                        CLERK            1300
10                                        MANAGER          2450
10                                        PRESIDENT        5000
10                                         all  job          8750
20                                        CLERK            1900
20                                        ANALYST          6000
20                                        MANAGER          2975
20                                         all  job         10875
30                                        CLERK             950
30                                        MANAGER          2850
30                                        SALESMAN         5600
30                                         all  job          9400
all  deptno                                all  job         29025

5. use hint
当多表连接很慢时,用ORDERED提示试试,也许会快很多

SELECT   /**/ /*+ ORDERED */ *  
   FROM  a, b, c, d 
  WHERE