?

使用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??

轉載于:https://www.cnblogs.com/lancelang/archive/2007/10/30/942920.html

總結

以上是生活随笔為你收集整理的Oracle高级查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。