注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。
EMP表的详细:
查询所有信息，
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1110 张三 主管 3322 12-3月 -14 5200 20
1111 李四 销售 3321 03-11月-15 3400 500 30
1112 王五 销售 3321 25-4月 -12 4400 800 30
1113 赵二 后勤 3320 30-5月 -11 3450 40
1114 李磊磊 会计 3319 22-12月-15 2500 50
1115 张少丽 销售 3321 11-3月 -16 3400 1400 30
1116 林建国 主管 3322 22-1月 -16 5700 20
1117 马富邦 后勤 3320 22-7月 -13 2800 40
1118 沈倩 会计 3319 06-5月 -10 2100 50
已选择9行。
1.以指定的次序返回查询结果
实际提取数据或生产报表时，一般都要根据一定的顺序查看，比如，想查看单位所雇员工的信息。
EMPNO ENAME HIREDATE
---------- -------------------- --------------
1110 张三 12-3月 -14
1116 林建国 22-1月 -16
上面除了“order by hiredate ASC”的写法外，还可以写成“order by 3 ASC”，意思是按第三列排序。
EMPNO ENAME HIREDATE
---------- -------------------- --------------
1110 张三 12-3月 -14
1116 林建国 22-1月 -16
当取值不定时，用这种方法就很方便，比如，有时取sal，有时要取comm来显示:
EMPNO ENAME SAL
---------- -------------------- ----------
1110 张三 5200
1116 林建国 5700
EMPNO ENAME COMM
---------- -------------------- ----------
1111 李四 500
1112 王五 800
1115 张少丽 1400
注意:用数据来代替列位置只能用于order by子句中，其他地方都不能用。
2.按多个字段排序
要求:按部门编号升序，并按工资降序排列
排序时有两个关键字：ASC表示升序，DESC表示降序
EMPNO DEPTNO SAL ENAME JOB
---------- ---------- ---------- -------------------- ------------------
1116 20 5700 林建国 主管
1110 20 5200 张三 主管
1112 30 4400 王五 销售
1111 30 3400 李四 销售
1115 30 3400 张少丽 销售
1113 40 3450 赵二 后勤
1117 40 2800 马富邦 后勤
1114 50 2500 李磊磊 会计
1118 50 2100 沈倩 会计
已选择9行。
对于重复值的排序，原理就是把数据分成了几组，然后每组的数据再去排序。
3.按子串排序
使用员工号尾号(最后两位)速查员工可以很快查到，当然也可以使用缩减的子串进行排序。
员工号尾号 DEPTNO SAL ENAME JOB
---------------- ---------- ---------- -------------------- ------------------
10 20 5200 张三 主管
11 30 3400 李四 销售
12 30 4400 王五 销售
13 40 3450 赵二 后勤
14 50 2500 李磊磊 会计
注:substr(expr,number)是字符串截取函数
4.字符串一一替换函数translate
语法格式：translate(expr,from_string,to_string)
示例：
NEW_STR
----------------------------------
12 你好 2314567
from_string与to_string以字符为单位，对应字符一一替换。
如果to_string对应的位置没有字符，from_string中列出的字符也将会被消掉。
NEW_STR
----------------
你好
原因
首先创建View如下:
----------------
1110 张三
1111 李四
1112 王五
1113 赵二
1114 李磊磊
1115 张少丽
1116 林建国
1117 马富邦
1118 沈倩
已选择9行。
现在只有一个字段data，里面就是数字+空格+字母的组合，要求我们用data中的字母(也就是原来的ename)排序。
DATA ENAME
------------- -------------
1117 马富邦 马富邦
1113 赵二 赵二
1112 王五 王五
1118 沈倩 沈倩
1116 林建国 林建国
1114 李磊磊 李磊磊
1111 李四 李四
1115 张少丽 张少丽
1110 张三 张三
已选择9行。
6.处理排序空值
oracle默认排序空值在后面，想把空值放前，以前的做法是nvl(comm,-1)
ENAME SAL COMM ORDER_COL
-------------------- ---------- ---------- ----------
张三 5200 -1
李磊磊 2500 -1
马富邦 2800 -1
林建国 5700 -1
赵二 3450 -1
沈倩 2100 -1
李四 3400 500 500
王五 4400 800 800
张少丽 3400 1400 1400
已选择9行。
nvl(comm,-1)这种做法需要对列类型及其中保存的数据有所了解才行，而且保存的数据如果有变化，该语句就要重新维护。
其实可以用关键字NULLS FIRST和NULLS LAST。
(1)空值在前
ENAME SAL COMM
-------------------- ---------- ----------
张三 5200
李磊磊 2500
马富邦 2800
林建国 5700
赵二 3450
沈倩 2100
李四 3400 500
王五 4400 800
张少丽 3400 1400
已选择9行。
(2)空值在后
ENAME SAL COMM
-------------------- ---------- ----------
李四 3400 500
王五 4400 800
张少丽 3400 1400
林建国 5700
马富邦 2800
沈倩 2100
赵二 3450
张三 5200
李磊磊 2500
已选择9行。
7.根据条件取不同列中的值来排序
有时排序的要求会比较复杂，比如：领导对工资在2000到3000元之间的员工更感兴趣，于是要求工资在这个范围的员工要排在前面，便于优先查看。
我们可以在查询中新生成一列，用多列排序的方法处理：
编码 姓名 级别 工资
---------- -------------------- ---------- ----------
1118 沈倩 1 2100
1114 李磊磊 1 2500
1117 马富邦 1 2800
1115 张少丽 2 3400
1111 李四 2 3400
1113 赵二 2 3450
1112 王五 2 4400
1110 张三 2 5200
1116 林建国 2 5700
已选择9行。
也可以不显示级别，直接把case when 放在 order by中:
---------- -------------------- ----------
1118 沈倩 2100
1114 李磊磊 2500
1117 马富邦 2800
1115 张少丽 3400
1111 李四 3400
1113 赵二 3450
1112 王五 4400
1110 张三 5200
1116 林建国 5700
EMP表的详细:
查询所有信息，
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1110 张三 主管 3322 12-3月 -14 5200 20
1111 李四 销售 3321 03-11月-15 3400 500 30
1112 王五 销售 3321 25-4月 -12 4400 800 30
1113 赵二 后勤 3320 30-5月 -11 3450 40
1114 李磊磊 会计 3319 22-12月-15 2500 50
1115 张少丽 销售 3321 11-3月 -16 3400 1400 30
1116 林建国 主管 3322 22-1月 -16 5700 20
1117 马富邦 后勤 3320 22-7月 -13 2800 40
1118 沈倩 会计 3319 06-5月 -10 2100 50
已选择9行。
1.以指定的次序返回查询结果
实际提取数据或生产报表时，一般都要根据一定的顺序查看，比如，想查看单位所雇员工的信息。
SQL> select empno,ename,hiredate from emp where deptno = 20 order by hiredate ASC;
EMPNO ENAME HIREDATE
---------- -------------------- --------------
1110 张三 12-3月 -14
1116 林建国 22-1月 -16
上面除了“order by hiredate ASC”的写法外，还可以写成“order by 3 ASC”，意思是按第三列排序。
SQL> select empno,ename,hiredate from emp where deptno = 20 order by 3 ASC;
EMPNO ENAME HIREDATE
---------- -------------------- --------------
1110 张三 12-3月 -14
1116 林建国 22-1月 -16
当取值不定时，用这种方法就很方便，比如，有时取sal，有时要取comm来显示:
SQL> select empno,ename,sal from emp where deptno = 20 order by 3 ASC;
EMPNO ENAME SAL
---------- -------------------- ----------
1110 张三 5200
1116 林建国 5700
SQL> select empno,ename,comm from emp where deptno = 30 order by 3 ASC;
EMPNO ENAME COMM
---------- -------------------- ----------
1111 李四 500
1112 王五 800
1115 张少丽 1400
注意:用数据来代替列位置只能用于order by子句中，其他地方都不能用。
2.按多个字段排序
要求:按部门编号升序，并按工资降序排列
排序时有两个关键字：ASC表示升序，DESC表示降序
SQL> select empno,deptno,sal,ename,job from emp order by 2 ASC,3 DESC;
EMPNO DEPTNO SAL ENAME JOB
---------- ---------- ---------- -------------------- ------------------
1116 20 5700 林建国 主管
1110 20 5200 张三 主管
1112 30 4400 王五 销售
1111 30 3400 李四 销售
1115 30 3400 张少丽 销售
1113 40 3450 赵二 后勤
1117 40 2800 马富邦 后勤
1114 50 2500 李磊磊 会计
1118 50 2100 沈倩 会计
已选择9行。
对于重复值的排序，原理就是把数据分成了几组，然后每组的数据再去排序。
3.按子串排序
使用员工号尾号(最后两位)速查员工可以很快查到，当然也可以使用缩减的子串进行排序。
SQL> select substr(empno,-2) as 员工号尾号, deptno,sal,ename,job from emp where rownum <=5 order by 1;
员工号尾号 DEPTNO SAL ENAME JOB
---------------- ---------- ---------- -------------------- ------------------
10 20 5200 张三 主管
11 30 3400 李四 销售
12 30 4400 王五 销售
13 40 3450 赵二 后勤
14 50 2500 李磊磊 会计
注:substr(expr,number)是字符串截取函数
4.字符串一一替换函数translate
语法格式：translate(expr,from_string,to_string)
示例：
SQL> select translate('ab 你好 bcadefg','abcdefg','1234567') as new_str from dual;
NEW_STR
----------------------------------
12 你好 2314567
from_string与to_string以字符为单位，对应字符一一替换。
如果to_string对应的位置没有字符，from_string中列出的字符也将会被消掉。
SQL> select translate('ab 你好 bcadefg','1abcdefg','1') as new_str from dual;
NEW_STR
----------------
你好
原因
首先创建View如下:
SQL>create or replace view v
as
select empno || ' ' || ename as data from emp;
SQL>创建视图成功 SQL>select * from vDATA
----------------
1110 张三
1111 李四
1112 王五
1113 赵二
1114 李磊磊
1115 张少丽
1116 林建国
1117 马富邦
1118 沈倩
已选择9行。
现在只有一个字段data，里面就是数字+空格+字母的组合，要求我们用data中的字母(也就是原来的ename)排序。
SQL> select data, translate(data,'- 0123456789','-') as ename from v order by 2 DESC;
DATA ENAME
------------- -------------
1117 马富邦 马富邦
1113 赵二 赵二
1112 王五 王五
1118 沈倩 沈倩
1116 林建国 林建国
1114 李磊磊 李磊磊
1111 李四 李四
1115 张少丽 张少丽
1110 张三 张三
已选择9行。
6.处理排序空值
oracle默认排序空值在后面，想把空值放前，以前的做法是nvl(comm,-1)
SQL> select ename,sal,comm,nvl(comm,-1) order_col from emp order by 4;
ENAME SAL COMM ORDER_COL
-------------------- ---------- ---------- ----------
张三 5200 -1
李磊磊 2500 -1
马富邦 2800 -1
林建国 5700 -1
赵二 3450 -1
沈倩 2100 -1
李四 3400 500 500
王五 4400 800 800
张少丽 3400 1400 1400
已选择9行。
nvl(comm,-1)这种做法需要对列类型及其中保存的数据有所了解才行，而且保存的数据如果有变化，该语句就要重新维护。
其实可以用关键字NULLS FIRST和NULLS LAST。
(1)空值在前
SQL> select ename,sal,comm from emp order by 3 NULLS FIRST;
ENAME SAL COMM
-------------------- ---------- ----------
张三 5200
李磊磊 2500
马富邦 2800
林建国 5700
赵二 3450
沈倩 2100
李四 3400 500
王五 4400 800
张少丽 3400 1400
已选择9行。
(2)空值在后
SQL> select ename,sal,comm from emp order by 3 NULLS LAST;
ENAME SAL COMM
-------------------- ---------- ----------
李四 3400 500
王五 4400 800
张少丽 3400 1400
林建国 5700
马富邦 2800
沈倩 2100
赵二 3450
张三 5200
李磊磊 2500
已选择9行。
7.根据条件取不同列中的值来排序
有时排序的要求会比较复杂，比如：领导对工资在2000到3000元之间的员工更感兴趣，于是要求工资在这个范围的员工要排在前面，便于优先查看。
我们可以在查询中新生成一列，用多列排序的方法处理：
SQL> select empno as 编码,
ename as 姓名,
case when sal >= 2000 and sal < 3000 then 1 else 2 end as 级别,
sal as 工资
from emp
order by 3,4;
编码 姓名 级别 工资
---------- -------------------- ---------- ----------
1118 沈倩 1 2100
1114 李磊磊 1 2500
1117 马富邦 1 2800
1115 张少丽 2 3400
1111 李四 2 3400
1113 赵二 2 3450
1112 王五 2 4400
1110 张三 2 5200
1116 林建国 2 5700
已选择9行。
也可以不显示级别，直接把case when 放在 order by中:
SQL> select empno as 编码,
ename as 姓名,
sal as 工资
from emp
order by 3,case when sal >= 2000 and sal < 3000 then 1 else 2 end; 编码 姓名 工资
---------- -------------------- ----------
1118 沈倩 2100
1114 李磊磊 2500
1117 马富邦 2800
1115 张少丽 3400
1111 李四 3400
1113 赵二 3450
1112 王五 4400
1110 张三 5200
1116 林建国 5700
已选择9行。
转载请注明出处：http://blog.csdn.net/acmman/article/details/51038665
