按其数值字段对varchar进行排序

Question

我相信这以前也有人问过，但由于这个问题可以用许多不同的方式来表述，所以很难找到正确的答案。

我有一个带有varchar字段的orders表，它是用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

一只双面小提琴在这里。

我的问题是：

1. 使用订单分句实现这一目标的最简单方法是哪一种？
2. 如何使用此自定义排序添加有效的索引而不必修改表？

我希望至少保持第一个答案与数据库无关(这就是为什么我没有包含特定于db的标记)，但是如果不同的DBMS/版本可能有不同的最佳答案，那么让我们假设PostgreSQL 12。

Answer 1

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

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

在线示例

Answer 2

我有一个带有varchar字段的订单表.一年..。达什..。累进数值

对不起，但我认为这是将多个信息存储在一个数据库字段中的典型案例--这是一个糟糕的想法。

我见过的大多数订单表都只是使用单个数字字段来唯一地标识订单，并将订单日期作为一个单独的字段，如果需要的话，可以从该字段派生年份。