提问人：VegaStudios 提问时间：10/28/2023 最后编辑：Erwin BrandstetterVegaStudios 更新时间：10/29/2023 访问量：76
按包含字母和数字的属性排序
Ordering by attribute that contains letters and numbers
问：
我们需要订购房间号列表。您可以认为单位按数字排序，前面的字母可以作为前缀，例如 1、2、3、4、5、6、7、8、9、10、A1、A2、A3、A4、A5、A6、A7、A8、A9、A10、B1、B2、B3 等。
在阅读了MySQL的“排序依据”-正确排序字母数字之后，这是我尝试过的：
create table units (id integer, unit_number varchar(100));
insert into units (id, unit_number) values (1, 'A1');
insert into units (id, unit_number) values (2, 'A2');
insert into units (id, unit_number) values (3, 'A3');
insert into units (id, unit_number) values (4, 'A4');
insert into units (id, unit_number) values (5, 'A5');
insert into units (id, unit_number) values (6, 'A6');
insert into units (id, unit_number) values (7, 'A7');
insert into units (id, unit_number) values (8, 'A8');
insert into units (id, unit_number) values (9, 'A9');
insert into units (id, unit_number) values (10, 'A10');
insert into units (id, unit_number) values (11, 'B1');
insert into units (id, unit_number) values (12, 'B2');
insert into units (id, unit_number) values (13, 'B3');
insert into units (id, unit_number) values (14, 'B4');
insert into units (id, unit_number) values (15, 'B5');
insert into units (id, unit_number) values (16, 'B6');
insert into units (id, unit_number) values (17, 'B7');
insert into units (id, unit_number) values (18, 'B8');
insert into units (id, unit_number) values (19, 'B9');
insert into units (id, unit_number) values (20, 'B10');
select * from units ORDER BY LENGTH(unit_number), unit_number;
当我得到结果时，我会得到这样的排序：
| id | unit_number |
| -------- | -------------- |
| 1 | A1 |
| 2 | A2 |
| 3 | A3 |
| 4 | A4 |
| 5 | A5 |
| 6 | A6 |
| 7 | A7 |
| 8 | A8 |
| 9 | A9 |
| 10 | B1 |
| 11 | B2 |
| 12 | B3 |
| 13 | B4 |
| 14 | B5 |
| 15 | B6 |
| 16 | B7 |
| 17 | B8 |
| 18 | B9 |
| 19 | A10 |
| 20 | B10 |
如何重写此查询，以便排序将 A10 放在 A9 之后？这更多的是从用户的角度来看的期望。
答：
2赞
Frank Heikens
10/28/2023
#1
为此，您可以使用一些正则表达式来提取所需的部分并对其进行排序：
SELECT unit_number
FROM units
ORDER BY
SUBSTRING(unit_number FROM '^[A-Za-z]+'), -- This sorts the alphabetical part.
CAST(SUBSTRING(unit_number FROM '[0-9]+$') AS INTEGER); -- This sorts the numerical part.
这也解决了更多字母字符（如 AB123）的潜在问题
评论
0赞
VegaStudios
10/29/2023
到目前为止，这很有效，弗兰克。
0赞
Erwin Brandstetter
10/28/2023
#2
基本上，您需要将数字部分转换为数字类型才能对其进行相应的排序。自然排序是这里的关键词。请确保仅强制转换正确的数字文本，否则将引发异常。
如果全部由单个前导字母和尾随数字组成，则 left（） 和 right（） 是最简单和最快的：
SELECT unit_number
FROM units
ORDER BY left(unit_number, 1), right(unit_number, -1)::int;
如果唯一的规则是：“0-n 个字母，后跟 0-n 个数字”：
SELECT unit_number
FROM units
ORDER BY substring(unit_number, '^\D*')
, substring(unit_number, '\d+$')::int NULLS FIRST;
substring（） 返回模式的空字符串 （），如果没有找到字母（准确地说是非数字），则注意 '*'！）。这方便地首先进行排序。''\D*
同样的“技巧”不会用于转换为 ，因为对于类型无效。如果未找到尾随数字，则模式（注意“+”！）将生成一个值。这也是有效的，但这必须放在首位。 实现了这一点。看：integer''\d+nullintegernullNULLS FIRST
如果可以的话，你需要定义在哪里排序......unit_numbernull
或者，将整数部分包装在 COALESCE（） 中。相同的结果：
SELECT unit_number
FROM units
ORDER BY substring(unit_number, '^\D*')
, COALESCE(substring(unit_number, '\d+$')::int, -1);
评论
0赞
VegaStudios
10/29/2023
谢谢欧文，这些都是有用的例子。但是，当我更新示例中的表格以包含单位“1”、“2”和“10”时，我们最终回到了根本问题，即单位的顺序为 1、10、2 等。 小提琴
0赞
Erwin Brandstetter
10/29/2023
@VegaStudios：是的，我错过了没有字母和多个数字的案例。现在解决了这个问题。还要注意扩展的小提琴。
评论