在PostgreSQL查询中按降序聚合字符串 [英] Aggregate strings in descending order in a PostgreSQL query
问题描述
除了问题如何在PostgreSQL分组依据查询中串联字符串字段的字符串?
如何按降序对员工进行排序?
How can I sort employee in descending order?
我正在使用不支持 string_agg()
的PostgreSQL 8.4。我尝试使用以下方法,但不支持此方法:
I am using PostgreSQL 8.4 which doesn't support string_agg()
. I've tried to use the following, but it isn't supported:
array_to_string(array_agg(employee ORDER BY employee DESC), ',')
我希望能找到正确答案的提示。
I'd appreciate any hint to the right answer.
推荐答案
在PostgreSQL 9.0 或更高版本中,您可以聚合函数中的顺序元素:
In PostgreSQL 9.0 or later you can order elements inside aggregate functions:
SELECT company_id, array_agg(employee ORDER BY company_id DESC)::text
FROM tbl
GROUP BY 1;
不适用于PostgreSQL 8.4 。您必须预先订购要汇总的值。为此,请使用子选择或 CTE (8.4 +):
That's not available for PostgreSQL 8.4. You have to pre-order values to be aggregated. Use a subselect or CTE (8.4+) for this purpose:
SELECT company_id, array_agg(employee)::text
FROM (SELECT * FROM tbl ORDER BY company_id, employee DESC) x
GROUP BY 1;
我另外通过 company_id
订购,因为
我还使用了将数组强制转换为 text
( array_agg(employee):: text
),它为您提供了一个基本的,用逗号分隔的字符串,没有额外的空格,并且是最快的方法。
更复杂的格式,请像问题中一样使用 array_to_string(array_agg(employee),',')
。
I also use the "trick" of just casting the array to text
(array_agg(employee)::text
), which gives you a basic, comma-separated string without additional white space and is the fastest way.
For more sophisticated formatting, use array_to_string(array_agg(employee), ', ')
like you have in your question.
这篇关于在PostgreSQL查询中按降序聚合字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!