熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle高級查詢實例,提升效率

2013-11-13 22:22:30  來源: Oracle 

  使用Oracle特有的查詢語法 可以達到事半功倍的效果

  如下

   樹查詢

  create table tree (

  id number() not null primary key

  name varchar() not null

  super number() not null                // 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 = connect by prior id = suepr

   分頁查詢

  select * from (

  select my_table* rownum   my_rownum from (

  select name birthday from employee order by birthday

  ) my_table where rownum <

  ) where my_rownum >= ;

   累加查詢 以scottemp為例

  select empno ename sal sum(sal) over(order by empno) result from emp;

  EMPNO ENAME              SAL      RESULT

  

   SMITH                    

   ALLEN                  

   WARD                   

   JONES                  

   MARTIN                 

   BLAKE                 

   CLARK                 

   SCOTT                 

   KING                  

   TURNER                

   ADAMS                 

   JAMES                  

   FORD                  

   MILLER                

   高級group by

  select decode(grouping(deptno)all deptnodeptno) deptno

  decode(grouping(job)all jobjob) job

  sum(sal) sal

  from emp

  group by ROLLUP(deptnojob);

  DEPTNO                                    JOB               SAL

  

                                          CLERK          

                                          MANAGER        

                                          PRESIDENT      

                                         all job        

                                          CLERK          

                                          ANALYST        

                                          MANAGER        

                                         all job       

                                          CLERK           

                                          MANAGER        

                                          SALESMAN       

                                         all job        

  all deptno                               all job       

   use hint

  當多表連接很慢時用ORDERED提示試試也許會快很多

  SELECT /**//*+ ORDERED */*

  FROM a b c d

  WHERE


From:http://tw.wingwit.com/Article/program/Oracle/201311/18916.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.