PostgreSQL array_agg命令 [英] PostgreSQL array_agg order

问题描述

表'动物'：

animal_name animal_type
Tom         Cat
Jerry       Mouse
Kermit      Frog

查询：

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

预期结果：

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

推荐答案

如果您使用的是PostgreSQL版本< 9.0然后：

来自： 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（选择x FROM测试顺序BY y DESC）AS标签；

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

因此，在您的情况下，您将这样写：

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;

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

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 BY clause to the subquery.

这篇关于PostgreSQL array_agg命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！