在PostgreSQL中对使用array_agg创建的文本聚合进行排序 [英] Sort a text aggregate created with array_agg in postgresql
问题描述
我在postgresql中有一个表。下表动物将解释我的问题:
I have a table in postgresql. The following table "animals" will do to explain my problem:
name
------
tiger
cat
dog
现在我正在使用以下查询:
Now I am using the following query:
SELECT
array_to_string(array_agg("name"), ', ')
FROM
animals;
结果是:老虎,猫,狗。但是我想对聚合进行排序,然后将其转换为字符串。因此,这就是我希望得到的结果:
The result is: "tiger, cat, dog". But I would like to sort the aggregate, before it is converted into a string. So this is the result I am hoping for:
"cat, dog, tiger".
所以我如何在Postgresql 8.4中对字符串数组进行排序,然后再将其转换为字符串。行名称上的ORDER BY不起作用,并且内置的sort函数仅处理整数值。
So how can I sort an string array in postgresql 8.4 before converting it to a string. ORDER BY on the row "name" does not work and the built-in sort function processes only integer values.
任何人都知道,如何在纯SQL中解决此问题?
Anyone a good idea, how to solve this in pure SQL?
非常感谢
理查德
Thanx a lot Richard
推荐答案
此将在PostgreSQL 9.0中提供:
This will be available in PostgreSQL 9.0:
http://www.postgresql.org/docs/9.0/static/release-9-0.html ,E.1.3.6.1节。总计
http://www.postgresql.org/docs/9.0/static/release-9-0.html, Section E.1.3.6.1. Aggregates
与此同时,您可以执行以下操作来解决问题(尽管比较笨拙):
In the meantime, you could do something like this which may solve the problem (albeit clunky):
SELECT array_agg(animal_name)
FROM (
SELECT "name" AS animal_name
FROM animals
ORDER BY "name"
) AS sorted_animals;
这篇关于在PostgreSQL中对使用array_agg创建的文本聚合进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!