在SQL中对包含数字的字符串列进行排序? [英] Sorting string column containing numbers in SQL?
问题描述
我正在尝试对字符串列(包含数字的 )进行排序.
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 ---+
您会看到Mysql的自然排序算法是将a 12
放在a 1
之后(对于大多数应用都是可以的),但是我有独特的需求,所以我希望结果应该像这样排序
You see natural sorting algorithm of Mysql is placing a 12
after a 1
(which is ok for most apps), But I have unique needs, so I want result should be sorted like this.
+----------+
+-- name --+
+----------+
+-- a 1 ---+
+-- a 2 ---+
+-- a 3 ---+
+-- a 12 --+
仅使用SQL 是否可以,或者我必须在应用程序级别操纵结果集?
Is it possible with just SQL, or I have to manipulate result-set at application level?
推荐答案
继续进行假设,它总是WORD_space_NUMBER,应该可以:
Going on the assumption it's always WORD_space_NUMBER this should work:
SELECT *
FROM table
ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED)
使用 POSITION 来找到空格,SUBSTRING 抓取其后的数字,然后CAST 使其具有可比性.
Use POSITION to find the space, SUBSTRING to grab the number after it, and CAST to make it a comparable value.
如果该列有其他模式,请告诉我,我将尝试设计一种更好的解决方法.
If there is a different pattern to the column, let me know and I'll try to devise a better work-around.
编辑证明有效:
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)
忽略我的la脚表/列名,但给我正确的结果.还走得更远,添加了双重排序功能,以数字分隔字母前缀.
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.
编辑
SUBSTRING_INDEX
将使其更具可读性.
Edit
SUBSTRING_INDEX
will make it little more readable.
ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)
这篇关于在SQL中对包含数字的字符串列进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!