各种数据库产品数据按某字段Order by时null的位置问题

oracle、mssql、mysql三种数据库产品在order by时：
Oracle在Order by时缺省认为null是最大值，所以如果是ASC升序则排在最后，DESC降序则排在最前面；
mssql和mysql在order by时缺省认为null是最小值，所以如果是ASC升序则排在最前面，DESC降序则排在最后；

对任意的数据库产品，想要在按某字段排序时(不管是升序还是降序)，对字段值为null的记录的出现顺序做任意的安排，可以使用以下的通用做法：
  ORDER  BY  CASE  WHEN  Col  Is  NULL  Then  1/0  Else  0/1  End,Col  [asc/desc]
Example 1:
oracle order by ,按Col升序，但是Col为null的排最前：

ORDER BY CASE WHEN Col Is NULL Then 0 Else 1 End, Col

Example 2:
mssql/mysql order by，按Col升序，但是Col为null的排最后：

ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

参见：
http://sqlblog.com/blogs/denis_gobo/archive/2007/10/19/3048.aspx

引用

Sort Values Ascending But NULLS Last
This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning.
Oracle has this syntax: ORDER BY ColumnName NULLS LAST;
SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

The 2 approaches with a datetime data type

DECLARE @Temp table(Col datetime)
INSERT INTO @Temp VALUES(getdate())
INSERT INTO @Temp VALUES('2007-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2005-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2004-10-19 09:54:03.730')
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)


SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'9999-12-31 23:59:59.997')


SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col



The 2 approaches with an integer data type

DECLARE @Temp table(Col int)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(555)
INSERT INTO @Temp VALUES(444)
INSERT INTO @Temp VALUES(333)
INSERT INTO @Temp VALUES(5656565)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)


SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'2147483647')


SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

评论

1 楼 wcily123 2011-03-18  

3q 帮我大忙了