PostgreSQL: 如何连接 " group by " 结果集的行？

叁歲伎倆 2022-08-24 05:14 147阅读 0赞

如何连接 " group by" 结果集的行？ 有点像行列转换，但又不完全是，  
描述颇为费劲，举例如下：  
      
**假如一张表有以下数据：  **    
中国                 台北   
中国                 香港  
中国                 上海   
日本                 东京   
日本                 大阪

**要求得到如下结果集： **  
中国    台北,香港,上海   
日本    东京，大阪

此文介绍几种实现以上要求的方法: PostgreSQL 版本为 9.0  
       
**--创建初始表并插入数据**

<table style=""> 
 <tbody style="line-height:28px"> 
  <tr style="line-height:28px"> 
   <td style="line-height:28px">&nbsp;skytf=&gt; create table city (country character varying(64),city character varying(64));<br style="line-height:28px"> CREATE TABLE <p style="margin:0px 0px 10px; padding:0px; line-height:28px">skytf=&gt; insert into city values ('中国','台北');<br style="line-height:28px"> INSERT 0 1<br style="line-height:28px"> skytf=&gt; insert into city values ('中国','香港');<br style="line-height:28px"> INSERT 0 1<br style="line-height:28px"> skytf=&gt; insert into city values ('中国','上海');<br style="line-height:28px"> INSERT 0 1<br style="line-height:28px"> skytf=&gt; insert into city values ('日本','东京');<br style="line-height:28px"> INSERT 0 1<br style="line-height:28px"> skytf=&gt; insert into city values ('日本','大阪');<br style="line-height:28px"> INSERT 0 1</p> <p style="margin:0px 0px 10px; padding:0px; line-height:28px">skytf=&gt; select * From city;<br style="line-height:28px"> &nbsp;country | city&nbsp;<br style="line-height:28px"> ---------+------<br style="line-height:28px"> &nbsp;中国&nbsp;&nbsp;&nbsp; | 台北<br style="line-height:28px"> &nbsp;中国&nbsp;&nbsp;&nbsp; | 香港<br style="line-height:28px"> &nbsp;中国&nbsp;&nbsp;&nbsp; | 上海<br style="line-height:28px"> &nbsp;日本&nbsp;&nbsp;&nbsp; | 东京<br style="line-height:28px"> &nbsp;日本&nbsp;&nbsp;&nbsp; | 大阪<br style="line-height:28px"> (5 rows)</p> </td> 
  </tr> 
 </tbody> 
</table>

**一 方法一：使用聚集函数 array\_agg**  
**--1.1 函数说明**  
Function name     array\_agg  
Argument Type(s)  any   
Return Type       array of the argument type   
Description       input values, including nulls, concatenated into an array

**--1.2 测试函数**

<table style=""> 
 <tbody style="line-height:28px"> 
  <tr style="line-height:28px"> 
   <td style="line-height:28px">&nbsp;skytf=&gt; \df array_agg<br style="line-height:28px"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List of functions<br style="line-height:28px"> &nbsp;&nbsp; Schema&nbsp;&nbsp; |&nbsp;&nbsp; Name&nbsp;&nbsp;&nbsp; | Result data type | Argument data types | Type&nbsp;<br style="line-height:28px"> ------------+-----------+------------------+---------------------+------<br style="line-height:28px"> &nbsp;pg_catalog | array_agg | anyarray&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | anyelement&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | agg<br style="line-height:28px"> (1 row) <p style="margin:0px 0px 10px; padding:0px; line-height:28px">skytf=&gt; select country,array_agg(city) from city group by country;<br style="line-height:28px"> &nbsp;country |&nbsp;&nbsp;&nbsp; array_agg&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br style="line-height:28px"> ---------+------------------<br style="line-height:28px"> &nbsp;中国&nbsp;&nbsp;&nbsp; | {台北,香港,上海}<br style="line-height:28px"> &nbsp;日本&nbsp;&nbsp;&nbsp; | {东京,大阪}<br style="line-height:28px"> (2 rows)&nbsp;&nbsp;&nbsp;&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

备注： 这正是我们需要的结果，返回的结果类型为数组。  
            
**二 方法二：使用聚集函数 string\_agg   **  
**--2.1函数说明 **         
Function name     string\_agg(expression, delimiter)   
Argument Type(s)  text, text    
Return Type       text   
Description       input values concatenated into a string, separated by delimiter

**--2.2 测试函数**

<table style=""> 
 <tbody style="line-height:28px"> 
  <tr style="line-height:28px"> 
   <td style="line-height:28px">&nbsp;skytf=&gt; select country,string_agg(city,',') from city group by country;<br style="line-height:28px"> &nbsp;country |&nbsp;&nbsp; string_agg&nbsp;&nbsp;&nbsp;<br style="line-height:28px"> ---------+----------------<br style="line-height:28px"> &nbsp;中国&nbsp;&nbsp;&nbsp; | 台北,香港,上海<br style="line-height:28px"> &nbsp;日本&nbsp;&nbsp;&nbsp; | 东京,大阪<br style="line-height:28px"> (2 rows) <p style="margin:0px 0px 10px; padding:0px; line-height:28px">skytf=&gt; select country,string_agg(city,',') from city where country='中国' group by country;<br style="line-height:28px"> &nbsp;country |&nbsp;&nbsp; string_agg&nbsp;&nbsp;&nbsp;<br style="line-height:28px"> ---------+----------------<br style="line-height:28px"> &nbsp;中国&nbsp;&nbsp;&nbsp; | 台北,香港,上海<br style="line-height:28px"> (1 row)</p> </td> 
  </tr> 
 </tbody> 
</table>

备注；string\_agg 函数返回结果为 text 类型。

**三 方法三：自定义聚集函数**  
**--3.1 创建聚集函数**  
skytf=> CREATE AGGREGATE array\_accum(anyelement) (  
skytf(>     SFUNC = array\_append,  
skytf(>     STYPE = anyarray,  
skytf(>     INITCOND = '\{\}'  
skytf(> );  
CREATE AGGREGATE

**--3.2 测试函数**  
skytf=> select country,array\_accum(city) from city where country='中国' group by country;  
 country |   array\_accum      
\---------+------------------  
 中国    | \{台北,香港,上海\}  
(1 row)

备注：同样得到了预期的结果，返回的类型为数组。  
   
   
**四 参考**  
[http://www.postgresql.org/docs/9.0/static/functions-aggregate.html][http_www.postgresql.org_docs_9.0_static_functions-aggregate.html]  
[http://www.postgresql.org/docs/9.0/static/sql-createaggregate.html][http_www.postgresql.org_docs_9.0_static_sql-createaggregate.html]

[http_www.postgresql.org_docs_9.0_static_functions-aggregate.html]: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
[http_www.postgresql.org_docs_9.0_static_sql-createaggregate.html]: http://www.postgresql.org/docs/9.0/static/sql-createaggregate.html