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

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

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

或者

select empno,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,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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!