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的排最前:
Example 2:
mssql/mysql order by,按Col升序,但是Col为null的排最后:
参见:
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
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
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
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