MySQL 按一个数字排序，最后为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2051602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Orderby a number, Nulls last
提问by JonB
Currently I am doing a very basic OrderBy in my statement.
目前我正在做一个非常基本的 OrderBy 在我的声明中。
SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC
The problem with this is that NULL entries for 'position' are treated as 0. Therefore all entries with position as NULL appear before those with 1,2,3,4. eg:
问题在于“位置”的空条目被视为 0。因此，所有位置为 NULL 的条目都出现在 1、2、3、4 的条目之前。例如：
NULL, NULL, NULL, 1, 2, 3, 4
Is there a way to achieve the following ordering:
有没有办法实现以下排序：
1, 2, 3, 4, NULL, NULL, NULL.
回答by Jarred
MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:
MySQL 有一个未公开的语法来最后对空值进行排序。在列名前放置一个减号 (-) 并将 ASC 切换为 DESC：
SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC
It is essentially the inverse of position DESCplacing the NULL values last but otherwise the same as position ASC.
它本质上是position DESC将 NULL 值放在最后的反面，但在其他方面与position ASC.
A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by
回答by d-_-b
I found this to be a good solution for the most part:
我发现这在很大程度上是一个很好的解决方案：
SELECT * FROM table ORDER BY ISNULL(field), field ASC;
回答by DrewM
Something like
就像是
SELECT * FROM tablename where visible=1 ORDER BY COALESCE(position, 999999999) ASC, id DESC
Replace 999999999 with what ever the max value for the field is
用该字段的最大值替换 999999999
回答by sumeet
NULL LAST
空最后
SELECT * FROM table_name ORDER BY id IS NULL, id ASC
回答by Langdon
You can swap out instances of NULL with a different value to sort them first (like 0 or -1) or last (a large number or a letter)...
您可以用不同的值换出 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
回答by Rachit Patel
Try using this query:
尝试使用此查询：
SELECT * FROM tablename
WHERE visible=1
ORDER BY
CASE WHEN position IS NULL THEN 1 ELSE 0 END ASC,id DESC
回答by Seth
You can coalesceyour NULLs in the ORDER BYstatement:
您可以在语句中合并您的 NULL ORDER BY：
select * from tablename
where <conditions>
order by
coalesce(position, 0) ASC,
id DESC
If you want the NULLs to sort on the bottom, try coalesce(position, 100000). (Make the second number bigger than all of the other position's in the db.)
如果您希望 NULL 在底部排序，请尝试coalesce(position, 100000). （使第二个数字大于数据库中的所有其他数字position。）
回答by YasirPoongadan
SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN `position` = 0 THEN 'a' END , position ASC
回答by Danny Beckett
For a DATEcolumn you can use:
对于DATE列，您可以使用：
NULLS last:
最后为NULL：
ORDER BY IFNULL(`myDate`, '9999-12-31') ASC
Blanks last:
空白最后：
ORDER BY IF(`myDate` = '', '9999-12-31', `myDate`) ASC
回答by Nishu Garg
To achieve following result :
达到以下结果：
1, 2, 3, 4, NULL, NULL, NULL.
1, 2, 3, 4, NULL, NULL, NULL.
USE syntax, place -(minus sign)before field name and use inverse order_type(Like: If you want order by ASC order then use DESC or if you want DESC order then use ASC)
USE 语法，放置-(minus sign)在字段名称之前并使用 inverse order_type（例如：如果您希望按 ASC 顺序排序，则使用 DESC 或者如果您希望 DESC 顺序则使用 ASC）
SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC
SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC
