mysql对空的默认排序方式[duplicate]

你为什么不最后按空点?

SELECT * 
FROM tablename
WHERE visible = 1 
ORDER BY position ASC NULLS LAST, id DESC 

为了 DATE 可以使用的列:

最后的空值:

ORDER BY IFNULL(`myDate`, '9999-12-31') ASC

Blanks最后:

ORDER BY IF(`myDate` = '', '9999-12-31', `myDate`) ASC

SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN `position` = 0 THEN 'a' END , position ASC

您可以用不同的值交换null的实例,以首先(如0或-1)或最后(大数字或字母)对它们进行排序。

SELECT field1, IF(field2 IS NULL, 9999, field2) as ordered_field2
  FROM tablename
 WHERE visible = 1
 ORDER BY ordered_field2 ASC, id DESC

你可以 coalesce 你的零分 ORDER BY 声明:

select * from tablename
where <conditions>
order by
    coalesce(position, 0) ASC, 
    id DESC

如果你想让空值在底部排序,请尝试 coalesce(position, 100000) . (使第二个数字比所有其他数字都大 position 在数据库中。)

尝试使用此查询:

SELECT * FROM tablename
WHERE visible=1 
ORDER BY 
CASE WHEN position IS NULL THEN 1 ELSE 0 END ASC,id DESC

空末

SELECT * FROM table_name ORDER BY id IS NULL, id ASC

有点像

SELECT * FROM tablename where visible=1 ORDER BY COALESCE(position, 999999999) ASC, id DESC

将999999999替换为字段的最大值

我发现这在很大程度上是一个很好的解决方案:

SELECT * FROM table ORDER BY ISNULL(field), field ASC;

mysql有一个未记录的语法来对空值进行最后排序。在列名前放置减号(-),并将ASC切换到DESC:

SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC

它本质上是 position DESC 将空值放在最后,否则与 position ASC .

这里有一个很好的推荐信 http://troels.arvin.dk/db/rdbms#select-order_by