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 Employees ORDER BY em_code DESC 选择最后一条记录
2. 使用 reg exp 从 em_code 中剥离字母并存储在 ec_alpha
3. 将重新匹配部分转换为整数 ec_num
4. 递增一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?

推荐答案

原因是字符串按字母顺序排序(而不是您想要的数字顺序)并且 1 在 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.

用空字符串替换所有非数字后，只剩下数字.

After replacing every non-digit with the empty string, only digits remain.

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