SQLCookBook第二章学习日记4

2.5处理排序空值

问题：在emp中根据comm排序结果。但是，这个字段可以有空值。需要指定是否将控制排在最后。

解决方案：根据数据的显示方式，以及特定的rdbms排序空值的方式，可以按照升序或降序来对空值的列排序。

select ename,sal,comm from emp
    order by 3 

select ename,sal,comm from emp
    order by 3 desc

这种解决方案中，如果可为空值的列包含非空值，那么也可以根据要求，按升序或降序排序，这可能正是所期待的，也许不是。

如果希望空值的排序与非空值不同，例如，要以升序或降序方式来排序非空值，将空值放在最后，则可使用case表达式有条件的排序列。

DB2、MySQL、PostgreSQL 和SQL Server
使用case表达式来标记一个值是否为为NULL。这个标记有两个值，一个表示NULL，一个表示非NULL。这样，只要在order by子句中增加标记列，便可以很容易的控制空值是排在前面还是最后，而不会被非空值所干扰。

select ename,sal,comm 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  

Oracle
Oracle 8i Database以及较早版本的用户可以使用跟其他平台相同的解决方案。Oracle9i Database 及以后版本的用户可以在order by子句中使用NULLS FIRST或NULLS LAST，来确保NULL是首先排序，还是最后排序，而不必考虑非空值的排序方式。

select ename,sal,comm from emp
    order by comm nulls last

讨论：除非RDBMS提供了一种方式，可以很容易的将空值排最前或排在最后，而不必在同列修改非空值（例如Oracle）否则就需要一个附加的列。

注意：到编写本书的时候，DB2用户可以在窗口函数的over子句的order by子句中使用NULLS FIRST和NULLS LAST，而不是在order by子句中使用以作用于整个结果集

多加一列的目的是（只是在查询中，不是在表中），可以判别空值，并将它们排在一起，放在最前面或者最后面。对于非Oracle解决方案，下面的查询返回内联视图X的结果集：

select ename,sal,comm,
    case when comm is null then 0 else 1 end as is_null
from emp

根据is_null的值，可以很容易的将NULL排在最前面或最后面，而不影响comm如何排序

2.6 根据数据项的键排序

问题：要根据某些条件逻辑来排序。例如，如果JOB是”SALESMAN”,要根据comm来排序。

解决方案：在order by子句中使用case表达式

select ename,sal,job,comm from emp 
    order by case when job = 'SALESMAN' then omm else sal end

讨论：
可以使用 case表达式来动态改变如何对结果排序。传递给order by 的值类似这样：

select  ename,sal,job,comm,
    case when job = 'SALESMAN' then comm else sal end as ordered
        from emp
order by 5