2.1以指定的次序返回查询结果
问题
显示部门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
总结
- 使用order by子句可以对结果集进行排序。 默认情况下 升序排列，因此asc是可选的， 降序排列使用desc.
- . 不一定要指定排序所基于的列名，也可以给出这列的编号， 编号从1开始。
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
- 用数字来代替列位置只能用于order by 子句中，其他地方都不能用。
2.2按多个字段排序
问题
在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>
总结
多列排序时，若前面的列有重复值，后面的排序才有用，相当于是通过前面的列把数据分成了几组，然后每组的数据再按照后面的列进行排序。
在order by中，优先顺序是从左到右。 如果在select列表中使用的数字位置排序，那么这个数值不能大于select列表中项目的数目。
一般情况下都可以按照select列表中没有的列来排序，但必须显示的给出排序的列名。
如果在查询中使用group by 或者distinct，则不能按照select列中
2.3按子串排序
问题
按照字符串的某一部分对查询结果进行排序。
比如从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的子串字符，可以很容易的按照字符串的一部分来排序。
2.4 TRANSLATE
语法
TRANSLATE(string,from_str,to_str)
工具
返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。
TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。
Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。
总结
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 按数字和字母混合字符串中的字母排序
问题
现有字母和数字混合的数据，希望按照数字或者字母部分来排序。
数据集 如下：
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函数从每一行中去掉数字或者字符，这样就很容易的可以根据具体情况来排序。
2.6 处理排序空值 - nulls first 和 nulls last
问题
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是首先排序还是最后排序，而不必考虑非空值的排序方式。
2.7 根据条件取不同列中的值来排序
问题
要根据某些条件逻辑来排序，比如 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 ;
