表’动物’:
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
我可以确保在第一个聚合函数中的顺序将始终与第二个相同。
我的意思是我不想得到:
Tom;Jerry;Kermit,Frog;Mouse,Cat
如果你是一个Postgresql版本< 9.0然后: From:
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 array_to_string(array_agg(animal_name),';') animal_types FROM (SELECT animal_name,animal_type FROM animals) AS x;