group_concat是MysqL中的一个聚集函数，挺好用的，MysqL的group_concat使用可参考：http://my.oschina.net/Kenyon/blog/70480。在postgresql中实现这个功能倒也容易,可以用array的转换或者函数string_agg()来做。 @H_502_0@ @H_502_0@ DB环境：postgresql 9.1.2 @H_502_0@ @H_502_0@ 一.测试数据准备

postgres=# create table t_kenyon(id int,name text);
CREATE TABLE
postgres=# insert into t_kenyon values(1,'kenyon'),(1,'chinese'),'china'),('2','american'),('3','japan'),'russian');
INSERT 0 6
postgres=# select * from t_kenyon order by 1;
 id |   name   
----+----------
  1 | kenyon
  1 | chinese
  1 | china
  2 | american
  3 | japan
  3 | russian
(6 rows)

二.实现过程 @H_502_0@ @H_502_0@ 1.以逗号为分隔符聚集

postgres=# select array_to_string(ARRAY(SELECT unnest(array_agg(name))),',') from t_kenyon ;
               array_to_string               
---------------------------------------------
 kenyon,chinese,china,american,japan,russian
(1 row)

@H_502_0@ 2.结合order by排序

postgres=# select array_to_string(ARRAY(SELECT unnest(array_agg(name)) order by 1),') from t_kenyon;
               array_to_string               
---------------------------------------------
 american,kenyon,russian
(1 row)

@H_502_0@ 3.结合group聚集

postgres=# SELECT id,array_to_string(ARRAY(SELECT unnest(array_agg(name)) 
             ),') FROM t_kenyon GROUP BY id;
 id |   array_to_string    
----+----------------------
  1 | china,kenyon
  2 | american
  3 | japan,russian
(3 rows)

4.以其他分隔符聚集,如*_*

postgres=# SELECT id,array_to_string(ARRAY(SELECT unnest(array_agg(name)) 
             order by 1),'*_*') FROM t_kenyon GROUP BY id ORDER BY id;
 id |     array_to_string      
----+--------------------------
  1 | china*_*chinese*_*kenyon
  2 | american
  3 | japan*_*russian
(3 rows)

@H_502_0@ 还有一个函数更简:string_agg@H_502_0@

postgres=# create table t_test(id int,vv varchar(100));@H_502_0@ CREATE TABLE@H_502_0@ postgres=# insert into t_test values(1,'kk'),(2,'ddd'),'yy'),(3,'ty');@H_502_0@ INSERT 0 4 @H_502_0@ @H_502_0@ postgres=# select * from t_test; @H_502_0@ id | vv @H_502_0@ ----+----- @H_502_0@ 1 | kk @H_502_0@ 2 | ddd @H_502_0@ 1 | yy @H_502_0@ 3 | ty@H_502_0@ (4 rows) @H_502_0@ @H_502_0@ postgres=# select id,string_agg(vv,'*^*') from t_test group by id; @H_502_0@ id | string_agg @H_502_0@ ----+------------ @H_502_0@ 1 | kk*^*yy @H_502_0@ 2 | ddd @H_502_0@ 3 | ty@H_502_0@ (3 rows)@H_502_0@ @H_502_0@

@H_502_0@ @H_502_0@ 三.总结

postgresql也可以很简单的实现MysqL中的group_concat功能，而且更加丰富，可以基于此写一个同名的函数。