使用Oracle特有的查询语法,可以达到事半功倍的效果。
如下：
1. 树查询
create table tree (
id number(10) not null primary key,
name varchar2(100) not null,
super number(10) notnull// 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 employeeorder by birthday
) my_table where rownum < 120
) where my_rownum >= 100;
3. 累加查询, 以scott.emp为例
select empno,ename,sal,sum(sal) over(order by empno) resultfrom emp;
EMPNOENAMESAL RESULT
---------- ---------- ---------- ----------
7369SMITH800 800
7499ALLEN1600 2400
7521WARD1250 3650
7566JONES 29756625
7654MARTIN1250 7875
7698BLAKE2850 10725
7782CLARK2450 13175
7788SCOTT3000 16175
7839KING5000 21175
7844 TURNER1500 22675
7876ADAMS1100 23775
7900JAMES950 24725
7902FORD3000 27725
7934MILLER1300 29025
4. 高级group by
select decode(grouping(deptno),1,'all deptno',deptno) deptno,
decode(grouping(job),'alljob',job) job,
sum(sal) sal
from emp
group by ROLLUP(deptno,job);
DEPTNOJOBSAL
---------------------------------------- --------- ----------
10CLERK 1300
10MANAGER 2450
10PRESIDENT 5000
10all job 8750
20CLERK 1900
20ANALYST 6000
20MANAGER 2975
20all job 10875
30CLERK 950
30MANAGER 2850
30SALESMAN 5600
30all job 9400
all deptnoall job 29025
5. use hint
当多表连接很慢时,用ORDERED提示试试,也许会快很多 SELECT /**//*+ ORDERED */* FROM a,b,c,d WHERE