mysql string agg,如何使array_agg（）像mySQL中的group_concat（）一样工作

So I have this table:

create table test (

id integer,

rank integer,

image varchar(30)

);

Then some values:

id | rank | image

---+------+-------

1 | 2 | bbb

1 | 3 | ccc

1 | 1 | aaa

2 | 3 | c

2 | 1 | a

2 | 2 | b

I want to group them by id and concatenate the image name in the order given by rank. In mySQL I can do this:

select id,

group_concat( image order by rank asc separator ',' )

from test

group by id;

And the output would be:

1 aaa,bbb,ccc

2 a,b,c

Is there a way I can have this in postgresql?

If I try to use array_agg() the names will not show in the correct order and apparently I was not able to find a way to sort them. (I was using postgres 8.4 )

解决方案

In PostgreSQL 8.4 you cannot explicitly order array_agg but you can work around it by ordering the rows passed into to the group/aggregate with a subquery:

SELECT id, array_to_string(array_agg(image), ',')

FROM (SELECT * FROM test ORDER BY id, rank) x

GROUP BY id;

In PostgreSQL 9.0 aggregate expressions can have an ORDER BY clause:

SELECT id, array_to_string(array_agg(image ORDER BY rank), ',')

FROM test

GROUP BY id;