oracle 排序去掉空值,处理排序空值

SQL> select ename,sal,comm from emp order by 3 desc;

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

WARD 1250 500

ALLEN 1600 300

TURNER 1500 0

14 rows selected.

/* 如上显示，降序排列后奖金显示到了最后面,空值在上，这里需要把空值显示到下面*/

-- 可以使用nulls last函数处理，如果使用nulls first则和上面默认排序结果一样

SQL> select ename,sal,comm from emp order by 3 desc nulls last;

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

JONES 2975

SMITH 800

CLARK 2450

14 rows selected.