使用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 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|