有没有办法按包含数字的字符串排序，按数字的值? [英] Is there a way to sort by a string filed that contains number, by the value of the number?

问题描述

我有包含由字母和数字构成的列 record_id 的记录

I have records with column record_id which is constructed from letters and numbers

例如

a1
a2
a11
a12
b1

如果我对这个进行排序，我会得到

If i sort this I would get

a1
a11
a12
a2
b1

我想知道是否有办法按字母然后按数字的值对它进行排序，如下所示

i was wondering if there is a way to sort this by letter then by the value of the number as the following

a1
a2
a11
a12
b1

推荐答案

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(string CHAR(3) NOT NULL);

INSERT INTO my_table VALUES
('a2'),('a11'),('a12'),('b1');

select * FROM my_table ORDER BY string;
+--------+
| string |
+--------+
| a11    |
| a12    |
| a2     |
| b1     |
+--------+

select * FROM my_table ORDER BY string+0;
+--------+
| string |
+--------+
| a2     |
| a11    |
| a12    |
| b1     |
+--------+

现在认真考虑规范化您的数据.

Now seriously consider normalising your data.

这篇关于有没有办法按包含数字的字符串排序，按数字的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！