postgresql 按包含字母和数字的属性排序

6ju8rftf 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(267)

我们需要一个房间号的列表。你可以想象这些单元是数字排序的，前面可能有一个字母作为前缀，例如1，2，3，4，5，6，7，8，9，10，A1，A2，A3，A4，A5，A6，A7，A8，A9，A10，B1，B2，B3等。
在阅读MySQL 'Order By' - sorting alphanumeric correctly之后，我尝试了以下操作：

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之后？这更多的是从用户的Angular 来看。

postgresql

来源：https://stackoverflow.com/questions/77376113/ordering-by-attribute-that-contains-letters-and-numbers

2条答案

按热度按时间

t3irkdon1#

你可以使用一些正则表达式来提取你需要的部分并对它们进行排序：

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）回复(0）举报 2023-11-18

7hiiyaii2#

基本上，您需要将数字部分转换为数字类型以使其相应地排序。natural-sort是这里的关键词。请确保您只转换正确的数字文字，否则将引发异常。
如果all由单个前导字母和尾随数字组成，则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+（注意'+'！）将导致null值。这对integer也有效，但null必须在顶部排序。NULLS FIRST实现了这一点。请参阅：

按列ASC排序，但首先是NULL值？

如果unit_number可以从null开始开始，你需要定义在哪里排序。
或者，将整数部分 Package 在COALESCE()中。相同的结果：

SELECT unit_number
FROM   units
ORDER  BY substring(unit_number, '^\D*')
        , COALESCE(substring(unit_number, '\d+$')::int, -1);

型
fiddle的

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 按包含字母和数字的属性排序

2条答案

相关问题

热门标签

最新问答