英文:
Postgres - Sort order is wrong
问题
这个查询:
with labels (label) as (
values
('Asphalt Layer 2 - Labor (Hr)'::text),
('Asphalt Layer 2 - Labor Rate ($/Hr)'::text),
('Asphalt Layer 2 - Labor Cost'::text)
)
select *
from labels
order by 1;
按照以下顺序排序了数值:
Asphalt Layer 2 - Labor Cost
Asphalt Layer 2 - Labor (Hr)
Asphalt Layer 2 - Labor Rate ($/Hr)
正确的顺序是（也通过JavaScript检查过）:
Asphalt Layer 2 - Labor (Hr)
Asphalt Layer 2 - Labor Cost
Asphalt Layer 2 - Labor Rate ($/Hr)
我正在使用AWS RDS Postgres，版本如下:
`PostgreSQL 11.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit`
这导致了交叉表查询中的数值被转置。我在这里做错了什么？
英文:
This query:
with labels (label) as (
values
('Asphalt Layer 2 - Labor (Hr)'::text),
('Asphalt Layer 2 - Labor Rate ($/Hr)'::text),
('Asphalt Layer 2 - Labor Cost'::text)
)
select *
from labels
order by 1;
Sorts the values in this order:
Asphalt Layer 2 - Labor Cost
Asphalt Layer 2 - Labor (Hr)
Asphalt Layer 2 - Labor Rate ($/Hr)
The correct order is (checked with JavaScript, too):
Asphalt Layer 2 - Labor (Hr)
Asphalt Layer 2 - Labor Cost
Asphalt Layer 2 - Labor Rate ($/Hr)
I am using AWS RDS Postgres with the following version:
PostgreSQL 11.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
This is causing the values in a crosstab query to be transposed. What am I doing wrong here?
答案1
得分: 2
没有单一的正确排序文本的方法：即不同语言会有不同的规则。因此，您必须明确告知您想要的排序顺序：
- 在PostgreSQL中，您可以使用collations
- JavaScript有localeCompare
英文:
There is no single way to sort text correctly: i.e. different languages will have different rules. So you must explicitly tell which sort order you want:
-
in postgres you can use collations
-
JavaScript has localeCompare
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论