PostgreSQL中的group_concat使用 · postgresql手冊

group_concat是mysql中的一個聚集函數，挺好用的，mysql的group_concat使用可參考：http://my.oschina.net/Kenyon/blog/70480。在postgresql中實現這個功能倒也容易,可以用array的轉換或者函數string_agg()來做。? DB環境：postgresql 9.1.2? **一.測試數據準備** ~~~ postgres=# create table t_kenyon(id int,name text); CREATE TABLE postgres=# insert into t_kenyon values(1,'kenyon'),(1,'chinese'),(1,'china'),('2','american'),('3','japan'),('3','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) ~~~ **二.實現過程?** 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) ~~~ ?2.結合order by排序 ~~~ postgres=# select array_to_string(ARRAY(SELECT unnest(array_agg(name)) order by 1),',') from t_kenyon; array_to_string --------------------------------------------- american,china,chinese,japan,kenyon,russian (1 row) ~~~ ?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,chinese,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) ~~~ 還有一個函數更簡:string_agg <table style="BORDER-TOP: rgb(248,248,248) 1px solid; BORDER-RIGHT: rgb(248,248,248) 1px solid; WIDTH: 703px; WHITE-SPACE: normal; WORD-SPACING: 0px; BORDER-COLLAPSE: collapse; BORDER-BOTTOM: rgb(248,248,248) 1px solid; TEXT-TRANSFORM: none; COLOR: rgb(68,68,68); PADDING-BOTTOM: 0px; TEXT-ALIGN: left; PADDING-TOP: 0px; FONT: 12px/22px 微軟雅黑, Verdana, sans-serif, 宋體; PADDING-LEFT: 0px; MARGIN: 0px; BORDER-SPACING: 1px; BORDER-LEFT: rgb(248,248,248) 1px solid; WIDOWS: 1; LETTER-SPACING: normal; PADDING-RIGHT: 0px; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" bordercolor="#e53333" cellspacing="0" cellpadding="2" align="right" border="2"><tbody style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"><tr style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"><td style="BORDER-TOP: rgb(248,248,248) 1px solid; BORDER-RIGHT: rgb(248,248,248) 1px solid; BORDER-BOTTOM: rgb(248,248,248) 1px solid; PADDING-BOTTOM: 1px; PADDING-TOP: 1px; PADDING-LEFT: 2px; MARGIN: 0px; BORDER-LEFT: rgb(248,248,248) 1px solid; PADDING-RIGHT: 2px">postgres=# create table t_test(id int,vv varchar(100));? CREATE TABLE? postgres=# insert into t_test values(1,'kk'),(2,'ddd'),(1,'yy'),(3,'ty');? INSERT 0 4 ? postgres=# select * from t_test; ? id | vv ?? ----+----- ?? 1 | kk ?? 2 | ddd ?? 1 | yy ?? 3 | ty? (4 rows) ? postgres=# select id,string_agg(vv,'*^*') from t_test group by id; ? id | string_agg ? ----+------------ ?? 1 | kk*^*yy ?? 2 | ddd ?? 3 | ty? (3 rows)? </td></tr></tbody></table> **三.總結** postgresql也可以很簡單的實現mysql中的group_concat功能，而且更加豐富，可以基于此寫一個同名的函數。