oracle如何查询排行第二,Oracle优化查询改写（第二章-给查询结果排序）

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;