Oracle优化查询改写（第二章-给查询结果排序）

2019独角兽企业重金招聘Python工程师标准>>>

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

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

或者

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

2.2按多个字段排序（略）

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

select translate('ab你好bcadefg','abcdefg','1234567') AS NEW_STR from dual;

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

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

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

2.6 处理空值排序

    空值在前

 

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

    空值在后

    

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

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;    

 

转载于:https://my.oschina.net/projerry/blog/1584216