PostgreSQL array_agg 顺序

PostgreSQL array_agg order

提问by Olo

Table 'animals':

表'动物'：

animal_name animal_type
Tom         Cat
Jerry       Mouse
Kermit      Frog

Query:

询问：

SELECT 
array_to_string(array_agg(animal_name),';') animal_names,
array_to_string(array_agg(animal_type),';') animal_types
FROM animals;

Expected result:

预期结果：

Tom;Jerry;Kerimt, Cat;Mouse;Frog
OR
Tom;Kerimt;Jerry, Cat;Frog;Mouse

Can I be sure that order in first aggregate function will always be the same as in second. I mean I would't like to get:

我可以确定第一个聚合函数中的顺序将始终与第二个聚合函数中的顺序相同。我的意思是我不想得到：

Tom;Jerry;Kermit, Frog;Mouse,Cat

采纳答案by UlfR

If you are on a PostgreSQL version < 9.0 then:

如果您使用的 PostgreSQL 版本 < 9.0，则：

From: http://www.postgresql.org/docs/8.4/static/functions-aggregate.html

来自：http: //www.postgresql.org/docs/8.4/static/functions-aggregate.html

In the current implementation, the order of the input is in principle unspecified. Supplying the input values from a sorted subquery will usually work, however. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

在当前的实现中，输入的顺序原则上是未指定的。然而，从排序的子查询提供输入值通常会起作用。例如：

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS 选项卡；

So in your case you would write:

所以在你的情况下，你会写：

SELECT
array_to_string(array_agg(animal_name),';') animal_names,
array_to_string(array_agg(animal_type),';') animal_types
FROM (SELECT animal_name, animal_type FROM animals) AS x;

The input to the array_agg would then be unordered but it would be the same in both columns. And if you like you could add an ORDER BYclause to the subquery.

array_agg 的输入将是无序的，但在两列中都是相同的。如果你愿意，你可以ORDER BY在子查询中添加一个子句。

回答by Frank Heikens

Use an ORDER BY, like this example from the manual:

使用 ORDER BY，就像手册中的这个例子：

SELECT array_agg(a ORDER BY b DESC) FROM table;