主要方法是通过使用CASE表达式来“标记”一个值是否为NULL。这里标记有两个值,一个表示NULL,一个表示非NULL。这样,只要在ORDER BY子句中增加标记列,便可以很容易的控制空值是排在前面还是排在后面,而不会被空值所干扰。
- SQL> select ename,sal,comm from emp;
- ENAME SAL COMM
- ---------- ---------- ----------
- SMITH 800
- ALLEN 1600 300
- WARD 1250 500
- JONES 2975
- MARTIN 1250 1400
- BLAKE 2850
- CLARK 2450
- SCOTT 3000
- KING 5000
- TURNER 1500 0
- ADAMS 1100
- ENAME SAL COMM
- ---------- ---------- ----------
- JAMES 950
- FORD 3000
- MILLER 1300
- 14 rows selected.
- //非空值按升序排序 空值排最后
- SQL> select ename,sal,comm from
- 2 (
- 3 select ename,sal,comm,
- 4 case when comm is null then 0 else 1 end as is_null
- 5 from emp
- 6 ) x
- 7 order by is_null desc,comm
- 8 ;
- 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
- ENAME SAL COMM
- ---------- ---------- ----------
- JONES 2975
- SMITH 800
- CLARK 2450
- 14 rows selected.
- //非空值按降序排序 空值排最后
- SQL> select ename,sal,comm from
- 2 (
- 3 select ename,sal,comm,
- 4 case when comm is null then 0 else 1 end as is_null
- 5 from emp
- 6 ) x
- 7 order by is_null desc,comm desc
- 8 ;
- ENAME SAL COMM
- ---------- ---------- ----------
- MARTIN 1250 1400
- WARD 1250 500
- ALLEN 1600 300
- TURNER 1500 0
- SCOTT 3000
- KING 5000
- ADAMS 1100
- JAMES 950
- FORD 3000
- MILLER 1300
- BLAKE 2850
- ENAME SAL COMM
- ---------- ---------- ----------
- JONES 2975
- SMITH 800
- CLARK 2450
- 14 rows selected.
- //非空值按升序排序 空值排最前面
- SQL> select ename,sal,comm from
- 2 (
- 3 select ename,sal,comm,
- 4 case when comm is null then 0 else 1 end as is_null
- 5 from emp
- 6 ) x
- 7 order by is_null,comm
- 8 ;
- 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
- ENAME SAL COMM
- ---------- ---------- ----------
- ALLEN 1600 300
- WARD 1250 500
- MARTIN 1250 1400
- 14 rows selected.
- //非空值按降序排序 空值排最前面
- SQL> select ename,sal,comm from
- 2 (
- 3 select ename,sal,comm,
- 4 case when comm is null then 0 else 1 end as is_null
- 5 from emp
- 6 ) x
- 7 order by is_null,comm desc
- 8 ;
- ENAME SAL COMM
- ---------- ---------- ----------
- SMITH 800
- CLARK 2450
- FORD 3000
- JAMES 950
- ADAMS 1100
- JONES 2975
- BLAKE 2850
- MILLER 1300
- SCOTT 3000
- KING 5000
- MARTIN 1250 1400
- ENAME SAL COMM
- ---------- ---------- ----------
- WARD 1250 500
- ALLEN 1600 300
- TURNER 1500 0
- 14 rows selected.
- 其实在ORACLE中,可以使用NULLS FIRST和NULLS LAST来实现相同功能
- 下面四条语句分别和上面四种写法相对应
- select ename,sal,comm
- from emp
- order by comm nulls last
- select ename,sal,comm
- from emp
- order by comm desc nulls last
- select ename,sal,comm
- from emp
- order by comm nulls first
- select ename,sal,comm
- from emp
- order by comm desc nulls first