2.5 处理排序空值

2.5 处理排序空值
Q: 指定是否将空值字段行排在最后
A: 使用 case 表达式在 order by 子句中增加标记列；或 RDBMS 特殊方案

DB2, MySQL, PostgreSQL, SQL Server, Oracle
/* all nulls last */
select ename, sal, comm, is_null
from (
select ename, sal, comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x
order by is_null desc, comm

ename | sal | comm | is_null
--------+------+------+---------
TURNER | 1500 | 0 | 1
ALLEN | 1600 | 300 | 1
WARD | 1250 | 500 | 1
MARTIN | 1250 | 1400 | 1
SCOTT | 3000 | | 0
KING | 5000 | | 0
ADAMS | 1100 | | 0
JAMES | 950 | | 0
FORD | 3000 | | 0
SMITH | 800 | | 0
MILLER | 1300 | | 0
JONES | 2975 | | 0
BLAKE | 2850 | | 0
CLARK | 2450 | | 0
(14 rows)

/* all nulls first */
select ename, sal, comm, is_null
from (
select ename, sal, comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x
order by is_null, comm

ename | sal | comm | is_null
--------+------+------+---------
MILLER | 1300 | | 0
ADAMS | 1100 | | 0
JAMES | 950 | | 0
FORD | 3000 | | 0
SMITH | 800 | | 0
JONES | 2975 | | 0
BLAKE | 2850 | | 0
CLARK | 2450 | | 0
SCOTT | 3000 | | 0
KING | 5000 | | 0
TURNER | 1500 | 0 | 1
ALLEN | 1600 | 300 | 1
WARD | 1250 | 500 | 1
MARTIN | 1250 | 1400 | 1

Oracle 9i or later (使用 "nulls last", "nulls first")
/* all nulls last */
select ename, sal, comm
from emp
order by comm nulls last

/* all nulls first */
select ename, sal, comm
from emp
order by comm nulls first

Tables Used:
for MySQL
http://www.hooto.com/home/rui/doc/archives/5089.html
for PostgreSQL
http://www.hooto.com/home/rui/doc/archives/5090.html

--EOF--