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