MySQL排序依据(str到int) [英] MySQL order by (str to int)

问题描述

SELECT `01` FROM perf WHERE year = '2013' order by CAST(`01` AS INT) LIMIT 3

第01列的数值为varchar.我需要将"01"的前3个定为整数.为什么此查询不起作用?

Column 01 has numeric values as varchar. I need to order top 3 of '01' as integer. Why doesn't this query working?

这样的表；

+----------------------+
| name | 01 | 02 | year|
+----------------------+
|name1 | 90 |*** |2013 |
+----------------------+
|name2 | 93 | 55 |2013 |
+----------------------+
|name3 |*** | 78 |2013 |
+----------------------+

查询应按01排序(取消 * )并提供名称和值.

Query should order by 01 (dismiss *) and give names and values.

推荐答案

MySQL不允许您使用CAST('01' AS INT).而是希望使用SIGNED或UNSIGNED.

MySQL doesn't permit you to CAST('01' AS INT). It expects instead a SIGNED or UNSIGNED.

SELECT `01` FROM perf WHERE year = '2013' order by CAST(`01` AS SIGNED) LIMIT 3

在CAST() 上查看 MySQL文档

mysql> SELECT CAST('01' AS SIGNED);
+----------------------+
| CAST('01' AS SIGNED) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

要强制非数字字符串最后排序，您将需要在ORDER BY中应用CASE，这将为它们分配一个非常高的值.该条件应测试，当转换为SIGNED时，01中的值不等于0，和，这是由于非数字的事实字符串将强制转换为零.

To force the non-numeric strings to be sorted last, you will need to apply a CASE in the ORDER BY which assigns them an absurdly high value. The condition should test that the value in 01 is not equal to 0, and when cast to a SIGNED the result is not 0, owing to the fact that non-numeric strings will cast to zero.

如果不满足这些条件，则假定该字符串为非数字字符串，并且在ORDER BY中给出的值为999999999，这会将它们推到末尾.随后由name对其进行排序.

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.

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

要使这些排序降序，请使用令人惊讶的低值(负数)而不是高值

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,

这篇关于MySQL排序依据(str到int)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！