文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >专栏 >Oracle查询优化-02给查询结果排序

Oracle查询优化-02给查询结果排序

作者头像
小小工匠
发布于 2021-08-16 15:09:35
发布于 2021-08-16 15:09:35
1.8K0
举报
文章被收录于专栏：小工匠聊架构小工匠聊架构
  • 1以指定的次序返回查询结果
    • 问题
    • 解决方案
    • 总结
  • 2按多个字段排序
    • 问题
    • 解决方案
    • 总结
  • 3按子串排序
    • 问题
    • 解决方案
    • 总结
  • 4 TRANSLATE
    • 语法
    • 工具
    • 总结
  • 5 按数字和字母混合字符串中的字母排序
    • 问题
    • 解决方案
    • 总结
  • 6 处理排序空值 - nulls first 和 nulls last
    • 问题
    • 解决方案
    • 总结
  • 7 根据条件取不同列中的值来排序
    • 问题
    • 解决方案
    • 总结

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

问题

显示部门10中的员工姓名、职位、工资，并且按照工资升序排列，结果集如下：

代码语言：javascript
复制
SQL> select  a.ename,a.job ,a.sal from emp  a where a.deptno=10;
ENAME JOB         SAL
---------- --------- ---------
CLARK MANAGER   2450.00
KING  PRESIDENT   5000.00
MILLER CLERK   1300.00

解决方案

使用order by 子句

代码语言：javascript
复制
SQL>  select  a.ename,a.job ,a.sal from emp  a where a.deptno=10 order by a.sal asc ;

ENAME JOB         SAL
---------- --------- ---------
MILLER CLERK   1300.00
CLARK MANAGER   2450.00
KING  PRESIDENT   5000.00

总结

  1. 使用order by子句可以对结果集进行排序。 默认情况下 升序排列，因此asc是可选的， 降序排列使用desc.
  2. . 不一定要指定排序所基于的列名，也可以给出这列的编号， 编号从1开始。
代码语言：javascript
复制
SQL>  select  a.ename,a.job ,a.sal from emp  a where a.deptno=10 order by  3 ;

ENAME JOB         SAL
---------- --------- ---------
MILLER CLERK   1300.00
CLARK MANAGER   2450.00
KING  PRESIDENT   5000.00
  1. 用数字来代替列位置只能用于order by 子句中，其他地方都不能用。

2.2按多个字段排序

问题

在emp表中，首先按照deptno升序排列，然后按照工资降序排列

解决方案

order by子句中列出不同的排序列，使用逗号分隔

代码语言：javascript
复制
SQL> select  a.deptno,a.sal from emp  a   order by a.deptno ,a.sal desc ;

DEPTNO       SAL
------ ---------
    10   5000.00
    10   2450.00
    10   1300.00
    20   3000.00
    20   3000.00
    20   2975.00
    20   1100.00
    20    800.00
    30   2850.00
    30   1600.00
    30   1500.00
    30   1250.00
    30   1250.00
    30    950.00

14 rows selected

SQL>

总结

  1. 多列排序时，若前面的列有重复值，后面的排序才有用，相当于是通过前面的列把数据分成了几组，然后每组的数据再按照后面的列进行排序。
  2. 在order by中，优先顺序是从左到右。 如果在select列表中使用的数字位置排序，那么这个数值不能大于select列表中项目的数目。
  3. 一般情况下都可以按照select列表中没有的列来排序，但必须显示的给出排序的列名。
  4. 如果在查询中使用group by 或者distinct，则不能按照select列中

2.3按子串排序

问题

按照字符串的某一部分对查询结果进行排序。

比如从emp中返回员工的名字和职位，并且按照职位字段的最后两个字符排序。

解决方案

在order by 子句中使用substr函数

代码语言：javascript
复制
SQL> select a.ename, a.job from emp a order by substr ( job, -2);
SQL> select a.ename, a.job from emp a order by substr ( job, length(job)-1);


SQL> select a.ename, a.job from emp a order by substr ( job, length(job)-1);

ENAME JOB
---------- ---------
ALLEN SALESMAN
MARTIN SALESMAN
WARD SALESMAN
TURNER SALESMAN
BLAKE MANAGER
JONES MANAGER
CLARK MANAGER
KING  PRESIDENT
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SCOTT ANALYST
FORD ANALYST

14 rows selected

SQL>

总结

使用dbms的子串字符，可以很容易的按照字符串的一部分来排序。

2.4 TRANSLATE

语法

代码语言：javascript
复制
TRANSLATE(string,from_str,to_str)

工具

  1. 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。
  2. TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。
  3. Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。

总结

代码语言：javascript
复制
SQL> SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; 

TRANSLATE('ABCDEFGHIJ','ABCDEF
------------------------------
123456ghij

SQL> select translate('abcbbaadef','bad','#@') from dual;

TRANSLATE('ABCBBAADEF','BAD','
------------------------------
@#c##@@ef

b将被＃替代，a将被＠替代，d对应的值是空值，将被移走。

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

问题

现有字母和数字混合的数据，希望按照数字或者字母部分来排序。

数据集 如下：

代码语言：javascript
复制
SQL> create or replace view v as select a.ename|| ' ' ||a.deptno  as data  from emp a ;

View created

SQL> select * from v ;

DATA
---------------------------------------------------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

14 rows selected

SQL>

解决方案

按照 deptno 排序

代码语言：javascript
复制
SQL> select *
  from v
 order by replace(data,
                  replace(translate(data, '0123456789', '##########'),
                          '#',
                          ''),
                  '');

DATA
---------------------------------------------------
CLARK 10
KING 10
MILLER 10
JONES 20
FORD 20
ADAMS 20
SMITH 20
SCOTT 20
WARD 30
TURNER 30
ALLEN 30
JAMES 30
BLAKE 30
MARTIN 30

14 rows selected

SQL>

按照 ename排序

代码语言：javascript
复制
SQL> select *
  from v
 order by replace(replace(translate(data, '0123456789', '##########'),
                         '#',
                         ''),
                 '');

DATA
---------------------------------------------------
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
SMITH 20
TURNER 30
WARD 30

14 rows selected

SQL>

总结

translate和replace函数从每一行中去掉数字或者字符，这样就很容易的可以根据具体情况来排序。

2.6 处理排序空值 - nulls first 和 nulls last

问题

emp表中comm字段，这个字段可以为空，需要指定是否将空值排在最后 或者将空值排在最前。

解决方案

oracle9i以后 可以使用关键字 nulls first 和 nulls last 来确保null是首先排序还是最后排序，而不必考虑非空值的排序方式。

代码语言：javascript
复制
SQL>  select ename ,comm from emp order by comm desc nulls first;

ENAME      COMM
---------- ---------
SMITH 
CLARK 
FORD 
JAMES 
ADAMS 
JONES 
BLAKE 
MILLER 
SCOTT 
KING  
MARTIN   1400.00
WARD    500.00
ALLEN    300.00
TURNER      0.00

14 rows selected

SQL> select ename ,comm from emp order by comm desc nulls last;

ENAME      COMM
---------- ---------
MARTIN   1400.00
WARD    500.00
ALLEN    300.00
TURNER      0.00
SCOTT 
KING  
ADAMS 
JAMES 
FORD 
MILLER 
BLAKE 
JONES 
SMITH 
CLARK 

14 rows selected

SQL>

总结

oracle9i以后 可以使用关键字 nulls first 和 nulls last 来确保null是首先排序还是最后排序，而不必考虑非空值的排序方式。

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

问题

要根据某些条件逻辑来排序，比如 job是saleman的要根据comm排序，否则按照sal排序，降序排列

解决方案

在order by 子句中使用case表达式

代码语言：javascript
复制
SQL>   select ename, job, sal, comm
    from emp
   order by case  
              when job = 'SALESMAN' then
               comm 
              else
               sal 
            end  desc;

ENAME JOB         SAL      COMM
---------- --------- --------- ---------
KING  PRESIDENT   5000.00 
FORD ANALYST   3000.00 
SCOTT ANALYST   3000.00 
JONES MANAGER   2975.00 
BLAKE MANAGER   2850.00 
CLARK MANAGER   2450.00 
MARTIN SALESMAN   1250.00   1400.00
MILLER CLERK   1300.00 
ADAMS CLERK   1100.00 
JAMES CLERK    950.00 
SMITH CLERK    800.00 
WARD SALESMAN   1250.00    500.00
ALLEN SALESMAN   1600.00    300.00
TURNER SALESMAN   1500.00      0.00

14 rows selected

SQL>

总结

可以使用CASE表达式来动态改变如何对结果排序。 传递给order by 的值类似这样：

代码语言：javascript
复制
  select ename, job, sal, comm,
  case  
              when job = 'SALESMAN' then
               comm 
              else
               sal 
               end  as ordered_col 
    from emp 
   order by  ordered_col   desc ;

或者

代码语言：javascript
复制
  select ename, job, sal, comm,
  case  
              when job = 'SALESMAN' then
               comm 
              else
               sal 
               end  as ordered_col 
    from emp 
   order by  5  desc ;
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2017/02/02 ，如有侵权请联系 cloudcommunity@tencent.com 删除
编程算法

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

编程算法
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
目录
  • 2.1以指定的次序返回查询结果
    • 问题
    • 解决方案
    • 总结
  • 2.2按多个字段排序
    • 问题
    • 解决方案
    • 总结
  • 2.3按子串排序
    • 问题
    • 解决方案
    • 总结
  • 2.4 TRANSLATE
    • 语法
    • 工具
    • 总结
  • 2.5 按数字和字母混合字符串中的字母排序
    • 问题
    • 解决方案
    • 总结
  • 2.6 处理排序空值 - nulls first 和 nulls last
    • 问题
    • 解决方案
    • 总结
  • 2.7 根据条件取不同列中的值来排序
    • 问题
    • 解决方案
    • 总结
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2026 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论