PostgreSQL资料集串接功能string_agg示例

oracle迁移时比较容易遇到wm_concat或者list_agg函数，在PG数据库里可以使用string_agg函数。string_agg函数提供将资料集组成字符串的串接功能，而且还支持简单的分组和排序。

建立测试环境

create table f1_driver (
  id serial,
  name varchar,
  team varchar,
  primary key(id)
);

insert into f1_driver(name,team) values 
('Ming.Yao','China')
,('s.vettel','Africa')
,('k.räikkönen','Africa')
,('ZhiYing.Lin','China');

查询测试资料表

postgres=# select * from f1_driver; 
 id |     name      |  team  
----+---------------+--------
  1 | Ming.Yao      | China
  2 | s.vettel      | Africa
  3 | k.räikkönen | Africa
  4 | ZhiYing.Lin   | China
(4 rows)

车手和车队的简单测试环境就准备好了

查询1笔资料

postgres=# select string_agg(name, ',') from f1_driver;
                 string_agg                  
---------------------------------------------
 Ming.Yao,s.vettel,k.räikkönen,ZhiYing.Lin
(1 row)

简单使用string_agg可以实现。

按照车队分组，查询2笔资料

postgres=# select string_agg(name, ',') from f1_driver group by team;
       string_agg       
------------------------
 Ming.Yao,ZhiYing.Lin
 s.vettel,k.räikkönen
(2 rows)

按照车队分组，组合字符串时以车手的id降序组成

postgres=# select string_agg (name,',' order by id desc) 
postgres-# from f1_driver
postgres-# group by team;
       string_agg       
------------------------
 k.räikkönen,s.vettel
 ZhiYing.Lin,Ming.Yao
(2 rows)

这样每个车队的二号车手可以排在前面。

按照车队分组，组合字符串时以车手的id降序组成

postgres=# select string_agg (name,',' order by id desc) 
postgres-# from f1_driver
postgres-# group by team;
       string_agg       
------------------------
 k.räikkönen,s.vettel
 ZhiYing.Lin,Ming.Yao
(2 rows)

这样每个车队的二号车手可以排在前面。

如果车手的name分first_name和last_name

create table f1_driver2 (
  id serial,
  first_name varchar,
  last_name varchar,
  team varchar,
  primary key (id)
);

insert into f1_driver2(first_name,last_name,team) values 
('Ming','Yao','China')
,('s','vettel','Africa')
,('k','räikkönen','Africa')
,('ZhiYing','Lin','China');

可以对first_name和last_name先连接再组合

select string_agg (
       first_name || ' ' || last_name,
	   ',' 
	   order by first_name,last_name) as names 
from f1_driver2
group by team;

查询结果如下

         names          
------------------------
 k räikkönen,s vettel
 Ming Yao,ZhiYing Lin
(2 rows)

总结

string_agg函数提供将资料集组成字符串的串接功能，支持分组和排序。