MySQL order by (str to int)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14709058/
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 order by (str to int)
提问by milesh
SELECT `01` FROM perf WHERE year = '2013' order by CAST(`01` AS INT) LIMIT 3
Column 01 has numeric values as varchar. I need to order top 3 of '01' as integer. Why doesn't this query working?
第 01 列的数值为 varchar。我需要将 '01' 的前 3 个排序为整数。为什么这个查询不起作用？
Table like this;
像这样的表；
+----------------------+
| name | 01 | 02 | year|
+----------------------+
|name1 | 90 |*** |2013 |
+----------------------+
|name2 | 93 | 55 |2013 |
+----------------------+
|name3 |*** | 78 |2013 |
+----------------------+
Query should order by 01 (dismiss *) and give names and values.
查询应按 01 (dismiss *)排序并给出名称和值。
回答by Michael Berkowski
MySQL doesn't permit you to CAST('01' AS INT). It expects instead a SIGNEDor UNSIGNED.
MySQL 不允许您使用CAST('01' AS INT). 它需要一个SIGNEDor UNSIGNED。
SELECT `01` FROM perf WHERE year = '2013' order by CAST(`01` AS SIGNED) LIMIT 3
Review the MySQL docs on CAST()for full details.
查看MySQL 文档以CAST()获取完整详细信息。
mysql> SELECT CAST('01' AS SIGNED);
+----------------------+
| CAST('01' AS SIGNED) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
To force the non-numeric strings to be sorted last, you will need to apply a CASEin the ORDER BYwhich assigns them an absurdly high value. The condition should test that the value in 01is not equal to 0, andwhen cast to a SIGNEDthe result is not 0, owing to the fact that non-numeric strings will cast to zero.
要强制将非数字字符串排在最后，您需要在 中应用 aCASE为ORDER BY它们分配一个高得离谱的值。条件应该测试中的值01是否不等于0，并且当转换为 a 时SIGNED，结果不是0，因为非数字字符串将转换为零。
If those conditions are not met, the string is assumed to be non-numeric, and given a value of 999999999 in the ORDER BY, which pushes them to the end. They're subsequently ordered by name.
如果不满足这些条件，则假定该字符串是非数字的，并在 中赋予 999999999 的值ORDER BY，这会将它们推到末尾。它们随后由 订购name。
SELECT * FROM perf
WHERE year = '2013'
ORDER BY
CASE WHEN (`01` <> '0' AND CAST(`01` AS SIGNED) <> 0) THEN CAST(`01` AS SIGNED) ELSE 999999999 END,
name
LIMIT 3
http://sqlfiddle.com/#!2/846e2/6
http://sqlfiddle.com/#!2/846e2/6
To make these sort descending, use an absurdly low value (negative) instead of a high value
要使这些排序降序，请使用低得离谱的值（负）而不是高值
CASE WHEN (`01` <> '0' AND CAST(`01` AS SIGNED) <> 0) THEN CAST(`01` AS SIGNED) ELSE -999999999 END DESC,
