1、 以指定的次序返回查询结果
SQL> SELECT ename,job,sal from emp where deptno = 10 order by sal asc;
ENAME JOB SAL
---------- --------- ----------
MILLER CLERK 1300
CLARK MANAGER 2450
KING PRESIDENT 5000
SQL> SELECT ename,job,sal from emp where deptno = 10 order by 3 asc;
ENAME JOB SAL
---------- --------- ----------
MILLER CLERK 1300
CLARK MANAGER 2450
KING PRESIDENT 5000
SQL>
2、按多个字段排序
SQL> SELECT empno,deptno,sal,ename,job from emp order by 2 asc,3 desc;
EMPNO DEPTNO SAL ENAME JOB
---------- ---------- ---------- ---------- ---------
7839 10 5000 KING PRESIDENT
7782 10 2450 CLARK MANAGER
7934 10 1300 MILLER CLERK
7788 20 3000 SCOTT ANALYST
7902 20 3000 FORD ANALYST
7566 20 2975 JONES MANAGER
7876 20 1100 ADAMS CLERK
7369 20 800 SMITH CLERK
7698 30 2850 BLAKE MANAGER
7499 30 1600 ALLEN SALESMAN
7844 30 1500 TURNER SALESMAN
7654 30 1250 MARTIN SALESMAN
7521 30 1250 WARD SALESMAN
7900 30 950 JAMES CLERK
14 rows selected.
SQL>
3、按子串排序
SQL> SELECT ename,job,substr(job,length(job)-1) 后两位 from emp order by 3;
ENAME JOB 后两位
---------- --------- ------------------------------------
ALLEN SALESMAN AN
MARTIN SALESMAN AN
WARD SALESMAN AN
TURNER SALESMAN AN
BLAKE MANAGER ER
JONES MANAGER ER
CLARK MANAGER ER
KING PRESIDENT NT
SMITH CLERK RK
ADAMS CLERK RK
JAMES CLERK RK
MILLER CLERK RK
SCOTT ANALYST ST
FORD ANALYST ST
14 rows selected.
SQL>
substr 的功能就是截取字符串
上面的SQL也可以写成如下所示,结果同上。
SQL> select ename,job,substr(job,-2) 后两位 from emp order by 3;
ENAME JOB 后两位
---------- --------- --------
ALLEN SALESMAN AN
MARTIN SALESMAN AN
WARD SALESMAN AN
TURNER SALESMAN AN
BLAKE MANAGER ER
JONES MANAGER ER
CLARK MANAGER ER
KING PRESIDENT NT
SMITH CLERK RK
ADAMS CLERK RK
JAMES CLERK RK
MILLER CLERK RK
SCOTT ANALYST ST
FORD ANALYST ST
14 rows selected.
SQL>
4、按数字字母混合字符串中的字母排序
5、处理排序空值
SQL>
SQL> SELECT ename,sal,comm from emp order by 3 nulls first;
ENAME SAL COMM
---------- ---------- ----------
SMITH 800
CLARK 2450
FORD 3000
JAMES 950
ADAMS 1100
JONES 2975
BLAKE 2850
MILLER 1300
SCOTT 3000
KING 5000
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
14 rows selected.
SQL> SELECT ename,sal,comm from emp order by 3 nulls last;
ENAME SAL COMM
---------- ---------- ----------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SCOTT 3000
KING 5000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
BLAKE 2850
JONES 2975
SMITH 800
CLARK 2450
14 rows selected.
SQL>
6、根据条件取不同列中的值来排序
SQL> SELECT ename as name,sal as salary,job as position,comm as bonus,case when job = 'SALESMAN' then comm else sal end as income from emp order by 5;
NAME SALARY POSITION BONUS INCOME
---------- ---------- --------- ---------- ----------
TURNER 1500 SALESMAN 0 0
ALLEN 1600 SALESMAN 300 300
WARD 1250 SALESMAN 500 500
SMITH 800 CLERK 800
JAMES 950 CLERK 950
ADAMS 1100 CLERK 1100
MILLER 1300 CLERK 1300
MARTIN 1250 SALESMAN 1400 1400
CLARK 2450 MANAGER 2450
BLAKE 2850 MANAGER 2850
JONES 2975 MANAGER 2975
SCOTT 3000 ANALYST 3000
FORD 3000 ANALYST 3000
KING 5000 PRESIDENT 5000
14 rows selected.
SQL> select ename as name,sal as salary,job as position,comm as bonus from emp order by (case when job = 'SALESMAN' then comm else sal end);
NAME SALARY POSITION BONUS
---------- ---------- --------- ----------
TURNER 1500 SALESMAN 0
ALLEN 1600 SALESMAN 300
WARD 1250 SALESMAN 500
SMITH 800 CLERK
JAMES 950 CLERK
ADAMS 1100 CLERK
MILLER 1300 CLERK
MARTIN 1250 SALESMAN 1400
CLARK 2450 MANAGER
BLAKE 2850 MANAGER
JONES 2975 MANAGER
SCOTT 3000 ANALYST
FORD 3000 ANALYST
KING 5000 PRESIDENT
14 rows selected.
SQL>