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

对任意的数据库产品，想要在按某字段排序时(不管是升序还是降序)，对字段值为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

分享到：

2009-08-21 13:18

浏览 3536

评论

1 楼

wcily123

2011-03-18

3q 帮我大忙了