ORACLE处理排序空值

主要方法是通过使用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