Oracle高级查询
来源:互联网 发布:教育cms 编辑:程序博客网 时间:2024/05/17 05:17
使用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
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;
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
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
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
FROM a, b, c, d
WHERE
本文转自:http://blog.csdn.net/sunangel/archive/2005/08/24/463589.aspx
- Oracle高级技巧,高级查询
- Oracle高级查询
- Oracle高级查询
- oracle 高级SQL查询
- Oracle高级查询
- Oracle的高级查询
- oracle的高级查询
- Oracle高级查询
- Oracle 高级查询
- Oracle高级查询
- Oracle高级查询加深
- ORACLE 高级查询
- oracle高级查询总
- Oracle 查询高级用法
- ORACLE 高级子查询
- oracle 高级查询
- oracle之高级查询
- Oracle 高级查询
- 转载一个手机RPG游戏制作工具,仿造RPGXP写的
- VS2008 对Office2010的自动化操作 word转pdf
- 首开博客
- 解决Silverlight无法调试的问题
- 同步测试
- Oracle高级查询
- 容器中控件的坐标问题(C#)
- 标签/TabActivity 深度研究
- 全新NV CUDA4.0版本令并行编程更轻松
- iLBC (internet Low Bitrate Codec)
- 关于ffdshow和ffmpeg的资料
- page replacement
- 经典——Android 布局
- Android命令大全