varchar 按其數字欄位排序

我敢肯定這已經被問過，但由於這個問題可以用許多不同的方式來表述，所以很難找到正確的答案。

我有一個訂單表，其中訂單號的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。

我會將該值轉換為整數數組，然後對該數組進行排序：

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

線上範例

您可以拆分數字並按其部分排序

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<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/280030