在 SQL 中对包含数字的字符串列进行排序?

Sorting string column containing numbers in SQL?(在 SQL 中对包含数字的字符串列进行排序?)

本文介绍了在 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 1之后放置a 12(这对大多数应用程序来说都可以),但我有独特的需求,所以我希望结果应该这样排序.

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)

忽略我蹩脚的表/列名称,但给了我正确的结果.还更进一步,添加了双重排序以将字母前缀与数字分开.

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 将使它更具可读性.

ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)

这篇关于在 SQL 中对包含数字的字符串列进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:在 SQL 中对包含数字的字符串列进行排序?

基础教程推荐