postgresql 将串联与 ORDER BY 结合使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18706562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combining concatenation with ORDER BY
提问by jutky
I have troubles in combining concatenation with order by in Postgre (9.1.9). Let's say, I have a table borders with 3 fields:
在 Postgre (9.1.9) 中，我在将串联与 order by 结合时遇到了麻烦。假设我有一个包含 3 个字段的表格边框：
Table "borders"
Column | Type | Modifiers
---------------+----------------------+-----------
country1 | character varying(4) | not null
country2 | character varying(4) | not null
length | numeric |
The first two fields are codes of the countries and the third one is the length of the border among those countries.
The primary key is defined on the first two fields.
前两个字段是国家代码，第三个字段是这些国家之间的边界长度。
主键定义在前两个字段上。
I need to compose a select of a column that would have unique values for the whole table, in addition this column should be selected in decreasing order. For this I concatenate the key fields with a separator character, otherwise two different rows might give same result, like (AB, C and A, BC).
我需要选择一个对整个表具有唯一值的列，此外，应按降序选择此列。为此，我将关键字段与分隔符连接起来，否则两个不同的行可能会给出相同的结果，例如（AB、C 和 A、BC）。
So I run the following query:
所以我运行以下查询：
select country1||'_'||country2 from borders order by 1;
However in the result I see that the '_' character is omited from the sorting. The results looks like this:
但是在结果中，我看到排序中省略了“_”字符。结果如下所示：
?column?
----------
A_CH
A_CZ
A_D
AFG_IR
AFG_PK
AFG_TAD
AFG_TJ
AFG_TM
AFG_UZB
A_FL
A_H
A_I
.
.
You can see that the result is sorted as if '_' doesn't exists in the strings.
您可以看到结果被排序，就好像字符串中不存在“_”一样。
If I use a letter (say 'x') as a separator - the order is correct. But I must use some special character that doesn't appear in the country1 and country2 fields, to avoid contentions.
如果我使用一个字母（比如“x”）作为分隔符 - 顺序是正确的。但是我必须使用一些没有出现在 country1 和 country2 字段中的特殊字符，以避免争用。
What should I do, in order to make the '_' character to be taken into account during the sorting.
我该怎么做，才能在排序过程中考虑到“_”字符。
EDIT
编辑
It turned out that the concatenation has nothing to do with the problem. The problem is that the order by simply ignores '_' character.
原来，串联与问题无关。问题是 order by 简单地忽略了 '_' 字符。
采纳答案by Roman Pekar
select country1 || '_' || country2 collate "C" as a
from borders
order by 1
Notes according to discussion in comments:
根据评论中的讨论注意事项：
1.) COLLATE "C"applies in the ORDER BYclause as long as it references the expression in the SELECTclause by positional parameteror alias. If you repeat the expression in ORDER BYyou also need to repeat the COLLATEclause if you want to affect the sort order accordingly.
1.)COLLATE "C"适用于ORDER BY子句，只要它SELECT通过位置参数或别名引用子句中的表达式。如果您重复中的表达式，如果您想相应地影响排序顺序，则ORDER BY还需要重复该COLLATE子句。
2.) In collations where _does not influence the sort order, it is more efficient to use fog's query, even more so because that one makes use of the existing index (primary key is defined on the first two fields).
However, if _has an influence, one needs to sort on the combined expression:
2.) 在_不影响排序顺序的排序规则中，使用雾的查询更有效，更有效的是因为它利用了现有的索引 ( primary key is defined on the first two fields)。
但是，如果_有影响，则需要对组合表达式进行排序：
Query performance (tested in Postgres 9.2):
sql fiddle demo
查询性能（在 Postgres 9.2 中测试）：
sql fiddle demo
回答by fog
Just order by the two columns:
只需按两列排序：
SELECT country1||'_'||country2 FROM borders ORDER BY country1, country2;
Unless you use aggregates or windows, PostgreSQL allows to order by columns even if you don't include them in the SELECT list.
除非您使用聚合或窗口，否则 PostgreSQL 允许按列排序，即使您没有将它们包含在 SELECT 列表中。
As suggested in another answer you can also change the collation of the combined column but, if you can, sorting on plain columns is faster, especially if you have an index on them.
正如另一个答案中所建议的，您还可以更改组合列的排序规则，但是，如果可以的话，对普通列进行排序会更快，尤其是当您有索引时。
回答by Tiny Haitian
What happens when you do the following?
当您执行以下操作时会发生什么？
select country1||'_'||country2 from borders order by country1||'_'||country2
My knowledge on order by 1 only does an ordinal sort. It won't do anything on concatenated columns. Granted, I'm speaking from SQL Server knowledge, so let me know if I'm way off base.
我对 order by 1 的了解只进行序数排序。它不会对连接的列执行任何操作。当然，我是根据 SQL Server 知识发言的，所以如果我离题了，请告诉我。
Edited:Ok; just saw Parado's post as I posted mine. Maybe you could create a view from this query (give it a column name) and then requery the view, order by that column? Or do the following:
编辑：好的；刚刚在我发布我的帖子时看到了 Parado 的帖子。也许您可以从此查询创建一个视图（给它一个列名），然后重新查询该视图，按该列排序？或执行以下操作：
select country_group from (
select country1||'_'||country2 as country_group from borders
) a
order by country_group
