【sql查询与优化】2.给查询结果排序

2021-11-13 16:54:46

注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。

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
----------------
你好

原因

5.按数字和字母混合字符串中的字母排序
首先创建View如下:

SQL>create or replace view v
    as 
    select empno || ' ' || ename as data from emp;

SQL>创建视图成功

SQL>select * from v

DATA
----------------
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

码农公寓

相关文章