PostgreSQL ORDER BY问题-自然排序 [英] PostgreSQL ORDER BY issue - natural sort

问题描述

我遇到了一个Postgres ORDER BY 问题，如下表：

I've got a Postgres ORDER BY issue with the following table:

em_code  name
EM001    AAA
EM999    BBB
EM1000   CCC

要向表中插入新记录，

1. 我使用 SELECT * FROM employee选择最后一条记录按em_code DESC命令排序


2. em_code中的条形字母使用正则表达式并存储在 ec_alpha
中

3. 将重映射部分转换为整数 ec_num


4. 加1 ec_num ++


5. 填充有足够的zeors并再次加前缀 ec_alpha

1. I select the last record with SELECT * FROM employees ORDER BY em_code DESC
2. Strip alphabets from em_code usiging reg exp and store in ec_alpha
3. Cast the remating part to integer ec_num
4. Increment by one ec_num++
5. Pad with sufficient zeors and prefix ec_alpha again

当 em_code 达到EM1000时，上述算法失败。

When em_code reaches EM1000, the above algorithm fails.

第一步将返回EM999而不是EM1000，它将再次生成EM1000作为新的 em_code ，从而打破了唯一键约束。

First step will return EM999 instead EM1000 and it will again generate EM1000 as new em_code, breaking the unique key constraint.

任何想法如何选择EM1000？

Any idea how to select EM1000?

推荐答案

原因是该字符串按字母顺序排序（而不是像您希望的那样按数字排序），并且在9之前排序。
您可以这样解决：

The reason is that the string sorts alphabetically (instead of numerically like you would want it) and 1 sorts before 9. You could solve it like this:

SELECT * FROM employees ORDER BY substring(em_code, 3)::int DESC

从 em_code 中删除​​冗余的 EM（如果可以的话）并保存一个整数开始会更有效

It would be more efficient to drop the redundant 'EM' from your em_code - if you can - and save an integer number to begin with.

到从字符串中删除所有非数字：

To strip any and all non-digits from a string:

SELECT regexp_replace(em_code, E'\\D','','g')
FROM employees

\D 是正则表达式类速记。

'g'作为第四个参数是全局开关申请

\D is the regular expression class-shorthand for "non-digits".
'g' as 4th parameter is the "globally" switch to apply the replacement to every occurrence in the string, not just the first.

因此，我用空字符串替换每个非数字，只提取字符串中的数字。

So I replace every non-digit with the empty string distilling solely digits from the string.

这篇关于PostgreSQL ORDER BY问题-自然排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！