【标 题】：Oracle高级查询
【关键字】：Oracle
【来 源】：http://blog.chinaunix.net/article.php?articleId=57734&blogId=11498
【关键字】：Oracle
【来 源】：http://blog.chinaunix.net/article.php?articleId=57734&blogId=11498
Oracle高级查询
Oracle高级查询
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 byselect 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 
使用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 = suepr2. 分页查询
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 290254. 高级group by
select decode(grouping(deptno),1,'all deptno',deptno) deptno, decode(grouping(job),1,'all job',job) job, sum(sal) salfrom emp group by ROLLUP(deptno,job);DEPTNO JOB SAL---------------------------------------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 all job 875020 CLERK 190020 ANALYST 600020 MANAGER 297520 all job 1087530 CLERK 95030 MANAGER 285030 SALESMAN 560030 all job 9400all deptno all job 290255. use hint
当多表连接很慢时,用ORDERED提示试试,也许会快很多
SELECT /**//*+ ORDERED */* FROM a, b, c, d WHERE
【相关文章】
Oracle CEO狂妄而真实的演讲
Oracle初学者笔记(九)--Oracle中的对象(可变数组)
Oracle初学者笔记(十)--Oracle中的对象(嵌套表与对象表)
Oracle初学者笔记(十一)--Oracle中的安全
Oracle初学者笔记(十二)--PL/SQL1_基础
Oracle初学者笔记(十三)--PL/SQL2_异常处理和游标
Oracle初学者笔记(十四)--子程序和程序包
Oracle初学者笔记(十五)--触发器和内置程序包
Oracle初学者笔记终结篇--集合和成员函数
Oracle专家调优秘密(转)
【随机文章】
wget 使用方法
使用rtsp-proxy突破电影站点的ip限制
系统学习Linux11点建议（转贴）
linux中的启动管理编辑器
甲骨文释出修补程序
防火墙的并发连接数
日记 [2006年05月10日] Linux中设置ip
读出数据库中的汉字并将声音数据存入
5iuml新增UMl资料下载
从VB 6.0到VB.NET的转换(1)
【相关评论】
没有相关评论
【发表评论】
|