主页 / dba / 问题 / 280030
Accepted
Claudio Floreani
Asked: 2020-11-21 04:15:59 +0800 CST

varchar 按其数字字段排序

  • 772

我敢肯定这已经被问过，但由于这个问题可以用许多不同的方式来表述，所以很难找到正确的答案。

我有一个订单表，其中订单号的varchar字段的格式为 4 位数年份、破折号 (-) 和渐进数值。例如，它可能包含以下值：

SELECT number FROM orders ORDER BY number LIMIT 10;

  number  
----------
 1999-13
 2019-11
 2020-1
 2020-10
 2020-100
 2020-12
 2020-2
 2020-21
 2020-3
 2021-1

我需要按年份对该字段进行排序，然后按累进数字排序，得到以下预期结果：

  number  
----------
 1999-13
 2019-11
 2020-1
 2020-2
 2020-3
 2020-10
 2020-12
 2020-21
 2020-100
 2021-1

一个 DB 小提琴就在这里。

我的问题是：

  1. 使用 ORDER 子条款实现此目的的最简单方法是什么？
  2. 如何使用此自定义排序添加高效索引而无需修改表？

我想至少将第一个答案保留为与数据库无关（这就是为什么我没有包含特定于 db 的标签的原因），但是如果不同的 DBMS/版本可能有不同的最佳答案，那么假设 PostgreSQL 12。

postgresql order-by

3 个回答

  1. Best Answer

    我会将该值转换为整数数组，然后对该数组进行排序：

    SELECT number 
FROM orders 
ORDER BY string_to_array(number, '-')::int[] 
LIMIT 10;

    在线示例

    • 4

  2. 您可以拆分数字并按其部分排序

    CREATE TABLE test  
    ("number" varchar(8))
;
    
INSERT INTO test  
    ("number")
VALUES
    ('1999-13'),
    ('2019-11'),
    ('2020-1'),
    ('2020-10'),
    ('2020-100'),
    ('2020-12'),
    ('2020-2'),
    ('2020-21'),
    ('2020-3'),
    ('2021-1')
;

    SELECT "number"
FROM test
ORDER BY 
split_part("number", '-', 1)::numeric,split_part("number", '-', 2)::numeric;

    | 号码 |
| :------- |
| 1999-13 |
| 2019-11 |
| 2020-1 |
| 2020-2 |
| 2020-3 |
| 2020-10 |
| 2020-12 |
| 2020-21 |
| 2020-100 |
| 2021-1 |

    db<>在这里摆弄

    • 0

  3. 我有一个订单表，其中包含订单号的 varchar 字段......年份......破折号......渐进数值

    抱歉，但我建议这是在单个数据库字段中存储多条信息的经典案例- 这是一个坏主意。

    我见过的大多数订单表都只是使用单个数字字段来唯一标识订单，并将订单日期作为单独的字段包含在内，如果需要，可以从中得出年份。

    • 0

相关问题