本文介绍了支持大数的自然排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一些这样的数据:
id | templateName ----+-------------- 10 | a 61 | a 63 | a 4 | a 6 | a 7 | a 34 | a 35 | a 62 | a 1 | a 13 | a 25 | a 26 | a 66 | a 68 | a 70 | a 65 | a 5 | a1 73 | a5 3 | a15 2 | a15a 69 | a15b 64 | a15b4 74 | a15b21 8 | a214748364我正在使用以下代码进行自然排序:
I'm doing natural sort with the following code:
CREATE TYPE ai AS (a text, i int); select id, "templateName" from daily_templates order by ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai FROM regexp_matches("templateName", '(\D*)(\d*)', 'g') x) , "templateName";它的工作原理就像上面的显示一样。现在我想支持大量数字,例如
And it works well like I show above. Now I want to support large numbers such as
a111111111111111111111,将超出整数的范围。我怎样才能做到这一点? 参考:人为或自然数混合的单词和数字字符串
which will be out of range of integer. How can I do that? Reference: Humanized or natural number sorting of mixed word-and-number strings
推荐答案它的工作方式类似于@clemens。在复合类型中使用数字(= 十进制):
It works like @clemens suggested. Use numeric (= decimal) in the composite type:
CREATE TYPE ai AS (a text, i numeric);db<>小提琴此处
我使用 int的原因引用的答案是性能。
The reason I used int in the referenced answer is performance.
更多推荐
支持大数的自然排序
发布评论