Oracle查询优化改写技巧与案例(第二章) —— 给查询结果排序

第2章给查询结果排序

2.1 以指定的次序返回查询结果

实际提取数据或生成报表时，一般都要根据一定的顺序査看，比如，想查看单位所雇员工的信息。

select empno, ename, hiredate from emp where deptno = 10 order by hiredate asc;

这种语句很多人都会写，但除了“0RDER BY hiredate ASC”这种写法外，还可以写成''〇RDER BY 3 ASC”，意思是按第三列排序

SQL> select empno, ename, hiredate from emp where deptno = 10 order by 3 asc;

     EMPNO ENAME      HIREDATE
    ------ ---------- --------------
      7782 CLARK      09-6月 -81
      7839 KING       17-11月-81
      7934 MILLER     23-1月 -82

当取值不定时，用这种方法就很方便，比如，有时取 sal ，有时要取 comm 来表示

SQL> select empno, ename, sal from emp where deptno = 10 order by 3 asc;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1300
      7782 CLARK            2450
      7839 KING             5000

对于这种需求，如果order By 后使用列名，就需要注意前后保持一致，否者就会给开发软件带来'小麻烦'，比如，开发初期的语句如下

str = ""
Str = Str & "select ename, hiredate, sal"
Str = Str & " from emp"
Str = Str & " order by ename"

后来要求增加 empno 的显示，而我们经常要按第一列来排序，于是需要修改为

str = ""
Str = Str & "select empno, ename, hiredate, sal"
Str = Str & " from emp"
Str = Str & " order by empno"

如果语句比较复杂，会经常忘记更改后面的 orderby，但使用“orderby3”这种方式就没问题。

需要注意的是，用数据来代替列位置只能用于 order by 子句中，其他地方都不能用。

2.2 按多个字段排序

如果按多列排序且有升有降怎么办？如：按部门编号升序，并按工资降序排列

排序时有两个关键字：AsC表示升序、DESC表示降序。

所以我们在 orderby 后加两列，并分别标明 ASC、DESC。

SQL> select empno, deptno, sal, ename, job from emp order by 2 asc, 3 desc;

     EMPNO     DEPTNO        SAL ENAME      JOB
---------- ---------- ---------- ---------- ---------
      7839         10       5000 KING       PRESIDENT
      7782         10       2450 CLARK      MANAGER
      7934         10       1300 MILLER     CLERK
      7788         20       3000 SCOTT      ANALYST
      7902         20       3000 FORD       ANALYST
      7566         20       2975 JONES      MANAGER
      7876         20       1100 ADAMS      CLERK
      7369         20        800 SMITH      CLERK
      7698         30       2850 BLAKE      MANAGER
      7499         30       1600 ALLEN      SALESMAN
      7844         30       1500 TURNER     SALESMAN
      7654         30       1250 MARTIN     SALESMAN

下面用图的形式进行介绍，如下图所示，多列排序时，若前面的列有重复值 (如deptno-10有3行数据)，后面的排序才有用。相当于是通过前面的列把数据分成了几组，然后每组的数据再按后面的列进行排序。

2.3 按子串排序

有一种速查法就是按顾客电话号码尾号的顺序记录, 这样在查找时候就可以快速收缩小查询范围，增强顾客的认可度，如果按照这种方式排序，应该怎么做呢？通过函数取出后面几位所需的信息即可。

select last_name as 名称,
       phone_number as 号码,
       salary as 工资,
       substr(phone_number, -4) as 尾号
  from hr.employees
 where rownum <= 5
 order by 4;

由此可见：只要能将数据查询出来，就能根据相应的信息排序。

2.4 TRANSLATE函数实战

语法格式：translate(expr, form_string, to_string)

示例如下：

SQL> select translate('ab 你好 bcadefg', 'abcdefg', '1234567') as new_str from dual;

    NEW_STR
    ---------------
    12 你好 2314567

如果为 to_string 为空，则返回空值。

SQL> select translate('ab 你好 bcadefg', 'abcdefg', '') as new_str from dual;

    NEW_STR
    ------------
    NULL

如果to_string对应的位置没有字符，删除 from_string 中列出的字符就会被消掉。

SQL> select translate('ab 你好 bcadefg', '1abcdefg', '1') as new_str from dual;

NEW_ST
------
 你好

2.5 按数字和字母混合字符串中的字母排序

首先创建 view 如下

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

SQL> select * from v;

    DATA
    ---------------------------------------------------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7844 TURNER
    7876 ADAMS
    7900 JAMES
    7902 FORD
    7934 MILLER

    已选择14行。

这个需求就难一点了，看到这里的字母（也就是原来的列 ename）吗？要求按照其中的字母（列ename）排序

那么就要先取出其中的字母才行，我们可以用translate的替换功能，把数字与空格都替换为空

SQL> select data, translate(data, '- 0123456789', '-') as ename from v order by 2;

    DATA                                                ENAME
    --------------------------------------------------- ------------------------------
    ----
    7876 ADAMS                                          ADAMS
    7499 ALLEN                                          ALLEN
    7698 BLAKE                                          BLAKE
    7782 CLARK                                          CLARK
    7902 FORD                                           FORD
    7900 JAMES                                          JAMES
    7566 JONES                                          JONES
    7839 KING                                           KING
    7654 MARTIN                                         MARTIN
    7934 MILLER                                         MILLER
    7788 SCOTT                                          SCOTT
    7369 SMITH                                          SMITH
    7844 TURNER                                         TURNER
    7521 WARD                                           WARD

    已选择14行。

2.6 处理排序空值

Oracle默认排序空值在后面，如果想把空值（如 emp.comm）显示在前面怎么办，用NVL(comm, -1)吗？

SQL> select ename, sal, comm, nvl(comm, -1) order_col from emp order by 4;

    ENAME             SAL       COMM  ORDER_COL
    ---------- ---------- ---------- ----------
    SMITH             800 NULL               -1
    CLARK            2450 NULL               -1
    FORD             3000 NULL               -1
    JAMES             950 NULL               -1
    ADAMS            1100 NULL               -1
    JONES            2975 NULL               -1
    BLAKE            2850 NULL               -1
    MILLER           1300 NULL               -1
    SCOTT            3000 NULL               -1
    KING             5000 NULL               -1
    TURNER           1500          0          0
    ALLEN            1600        300        300
    WARD             1250        500        500
    MARTIN           1250       1400       1400

    已选择14行。

也许很多人都是用这种方式，但是这种方法需要对列类型及其中保存的数据有所了解才行，而且保存的数据如果有变化，该语句就需要重新维护

其实可以使用关键字 NULLS FIRST 和 NULLS LAST

空值在前

SQL> select ename, sal, comm order_col from emp order by 3 nulls first;

    ENAME             SAL  ORDER_COL
    ---------- ---------- ----------
    SMITH             800 NULL
    CLARK            2450 NULL
    FORD             3000 NULL
    JAMES             950 NULL
    ADAMS            1100 NULL
    JONES            2975 NULL
    BLAKE            2850 NULL
    MILLER           1300 NULL
    SCOTT            3000 NULL
    KING             5000 NULL
    TURNER           1500          0
    ALLEN            1600        300
    WARD             1250        500
    MARTIN           1250       1400

    已选择14行。

空值在后

SQL> select ename, sal, comm order_col from emp order by 3 nulls last;

    ENAME             SAL  ORDER_COL
    ---------- ---------- ----------
    TURNER           1500          0
    ALLEN            1600        300
    WARD             1250        500
    MARTIN           1250       1400
    SCOTT            3000 NULL
    KING             5000 NULL
    ADAMS            1100 NULL
    JAMES             950 NULL
    FORD             3000 NULL
    MILLER           1300 NULL
    BLAKE            2850 NULL
    JONES            2975 NULL
    SMITH             800 NULL
    CLARK            2450 NULL

    已选择14行。

这样是不是方面了很多？

2.7 根据条件取不同列中的值来排序

有时排序的要求会比较负责，比如：领导对工资在1000到2000元之间的员工更感兴趣，于是要求工资在这个范围的员工要排在前面，以便优先查看

对于这种需求，我们可以在查询中新生成一列，用多列排序的方法处理

select empno as 编码,
       ename as 姓名,
       case
         when sal >= 1000 and sal < 2000 then 1 else 2
       end as 级别,
       sal as 工资
  from emp
 where deptno = 30
 order by 3, 4

可以看到，950 与 2850 都排在了后面，也可以不显示级别，直接把 case when 放在 order by 中

select empno as 编码,
       ename as 姓名,
       sal as 工资
  from emp
 where deptno = 30
 order by case when sal >= 1000 and sal < 2000 then 1 else 2 end, 3

未经允许请勿转载：程序喵 » Oracle查询优化改写技巧与案例(第二章) —— 给查询结果排序

程序喵

第2章给查询结果排序

2.1 以指定的次序返回查询结果

2.2 按多个字段排序

2.3 按子串排序

2.4 TRANSLATE函数实战

2.5 按数字和字母混合字符串中的字母排序

2.6 处理排序空值

2.7 根据条件取不同列中的值来排序

Oracle数据库建立索引的原则

Oracle竟然是世界上最大的开源公司

MongoDB常用语句

Oracle查询优化改写技巧与案例(第一章) —— 单表查询

Oracle查询优化改写技巧与案例 —— 目录

Oracle中scott用户创建视图权限不足(解决)

Oracle中coalesce函数的使用

Oracle中replace函数的使用

第2章 给查询结果排序

2.1 以指定的次序返回查询结果

2.2 按多个字段排序

2.3 按子串排序

2.4 TRANSLATE函数实战

2.5 按数字和字母混合字符串中的字母排序

2.6 处理排序空值

2.7 根据条件取不同列中的值来排序

Oracle数据库建立索引的原则

Oracle竟然是世界上最大的开源公司

MongoDB常用语句

Oracle查询优化改写技巧与案例(第一章) —— 单表查询

Oracle查询优化改写技巧与案例 —— 目录

Oracle中scott用户创建视图权限不足(解决)

Oracle中coalesce函数的使用

Oracle中replace函数的使用

第2章给查询结果排序