MySQL 在 SQL 中对包含数字的字符串列进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4939518/
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
Sorting string column containing numbers in SQL?
提问by Ish
I am trying to sort string column (containing numbers).
我正在尝试对字符串列（包含数字）进行排序。
// SELECT `name` FROM `mytable` ORDER BY `name` ASC
+----------+
+-- name --+
+----------+
+-- a 1 ---+
+-- a 12 --+
+-- a 2 ---+
+-- a 3 ---+
You see natural sorting algorithm of Mysql is placing a 12after a 1(which is ok for most apps), But I have unique needs, so I want result should be sorted like this.
你看到 Mysql 的自然排序算法放在a 12之后a 1（这对于大多数应用程序来说是可以的），但我有独特的需求，所以我希望结果应该像这样排序。
+----------+
+-- name --+
+----------+
+-- a 1 ---+
+-- a 2 ---+
+-- a 3 ---+
+-- a 12 --+
Is it possible with just SQL, or I have to manipulate result-set at application level?
是否可以仅使用 SQL，或者我必须在应用程序级别操作结果集？
回答by Brad Christie
Going on the assumptionit's always WORD_space_NUMBER this should work:
继续假设它总是 WORD_space_NUMBER 这应该有效：
SELECT *
FROM table
ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED)
Use POSITIONto find the space, SUBSTRINGto grab the number after it, and CASTto make it a comparable value.
使用POSITION查找空格，使用SUBSTRING获取其后的数字，并使用CAST使其成为可比较的值。
If there is a different pattern to the column, let me know and I'll try to devise a better work-around.
如果专栏有不同的模式，请告诉我，我会尝试设计更好的解决方法。
EDITProven to work:
编辑证明可以工作：
mysql> INSERT INTO t (st) VALUES ('a 1'),('a 12'),('a 6'),('a 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t ORDER BY st;
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 4 | a 11 |
| 2 | a 12 |
| 3 | a 6 |
+----+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 3 | a 6 |
| 4 | a 11 |
| 2 | a 12 |
+----+------+
mysql> INSERT INTO t (st) VALUES ('b 1'),('b 12'),('b 6'),('b 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 5 | b 1 |
| 3 | a 6 |
| 7 | b 6 |
| 4 | a 11 |
| 8 | b 11 |
| 2 | a 12 |
| 6 | b 12 |
+----+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM t ORDER BY LEFT(st,LOCATE(' ',st)), CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st |
+----+------+
| 1 | a 1 |
| 3 | a 6 |
| 4 | a 11 |
| 2 | a 12 |
| 5 | b 1 |
| 7 | b 6 |
| 8 | b 11 |
| 6 | b 12 |
+----+------+
8 rows in set (0.00 sec)
ignore my lame table/column names, but gives me the correct result. Also went a little further and added double sort to break letters prefix with numeric.
忽略我蹩脚的表/列名称，但给了我正确的结果。还更进一步，添加了双重排序以用数字打破字母前缀。
EditSUBSTRING_INDEXwill make it little more readable.
编辑SUBSTRING_INDEX将使其更具可读性。
ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)
回答by tzviki
You can try this:
你可以试试这个：
ORDER BY CASE
WHEN ISNUMERIC(column) THEN cast(column as int)
else ascii(column[1,1])
end
回答by Jim Brissom
Have a look at the MySQL CAST / Convertfunctions.
看看 MySQL CAST / Convert函数。
SELECT name FROM mytable ORDER BY CAST(name AS INTEGER) ASC;
Edit:I read:
编辑：我读到：
I am trying to sort string column (containing numbers).
我正在尝试对字符串列（包含数字）进行排序。
...but just took a look at the result set. Is the aactually also part of the contents? If so, you can use functions like MIDto extract only the numeric value and cast that.
...但只是看了一下结果集。是a居然还部分内容？如果是这样，您可以使用MID 之类的函数来仅提取数值并对其进行转换。
But if allrows contain just awithout variation, you might as well omit it...
但是，如果所有行都包含a没有变化，您也可以省略它......
回答by Andriy M
Another option could be to pad the string with spaces to the left of the number (i.e. add spaces between the word and the number) and use the resulting string for sorting, comething like this:
另一种选择是在数字左侧用空格填充字符串（即在单词和数字之间添加空格）并使用结果字符串进行排序，如下所示：
ORDER BY INSERT(
column,
LOCATE(' ', column),
0,
SPACES(20 - LENGTH(column) + LOCATE(' ', column))
)
The string is assumed to be of the pattern 'word followed by space(s) followed by number', and the number is assumed to be non-negative (or it would sort incorrectly). The hardcoded 20is chosen arbitrarily and is supposed to be the string's number part's maximum possible length.
字符串被假定为“单词后跟空格后跟数字”的模式，并且数字被假定为非负数（否则排序不正确）。硬编码20是任意选择的，应该是字符串的数字部分的最大可能长度。
回答by Hussain
Here i found another solution with following query using Convert
在这里，我找到了另一个使用 Convert 查询的解决方案
select * from tablename where columnname like '%a%' order by Convert(smallint,Replace(columnname,'a',''))
