显示部门10中的员工姓名、职位、工资,并且按照工资升序排列,结果集如下:
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 子句
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
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
在emp表中,首先按照deptno升序排列,然后按照工资降序排列
order by子句中列出不同的排序列,使用逗号分隔
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>
按照字符串的某一部分对查询结果进行排序。
比如从emp中返回员工的名字和职位,并且按照职位字段的最后两个字符排序。
在order by 子句中使用substr函数
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的子串字符,可以很容易的按照字符串的一部分来排序。
TRANSLATE(string,from_str,to_str)
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对应的值是空值,将被移走。
现有字母和数字混合的数据,希望按照数字或者字母部分来排序。
数据集 如下:
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 排序
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排序
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函数从每一行中去掉数字或者字符,这样就很容易的可以根据具体情况来排序。
emp表中comm字段,这个字段可以为空,需要指定是否将空值排在最后 或者将空值排在最前。
oracle9i以后 可以使用关键字 nulls first 和 nulls last 来确保null是首先排序还是最后排序,而不必考虑非空值的排序方式。
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是首先排序还是最后排序,而不必考虑非空值的排序方式。
要根据某些条件逻辑来排序,比如 job是saleman的要根据comm排序,否则按照sal排序,降序排列
在order by 子句中使用case表达式
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 的值类似这样:
select ename, job, sal, comm,
case
when job = 'SALESMAN' then
comm
else
sal
end as ordered_col
from emp
order by ordered_col desc ;
或者
select ename, job, sal, comm,
case
when job = 'SALESMAN' then
comm
else
sal
end as ordered_col
from emp
order by 5 desc ;