postgresql中将复数类型集合其中的字段拼接成一个字符串,string_agg
原本查出的结果
想要的结果
关键函数,string_agg,unnest
string_agg:将集合数据按指定字符串拼接成一个字符串,多行合并
unnest:将复合类型集合字段,转为临时表
先看看初步效果
select id, string_agg(name, ',') from
(
select id, ((unnest(feature_name)).name) as name from meta_contact_details
) s GROUP BY id
结果:这里,没有名称的没有被查出来
原因是unnest跳过了null所在行
改进一下
加上case when then
select id, string_agg(name, ',') from
(
select id, (case when feature_name is null then null else ((unnest(feature_name)).name) end) as name from meta_contact_details
) s GROUP BY id
结果
这里如果查的字段多了话就要重复,不安逸,换一种思路
最终sql
select id,
(select string_agg(name, ',') from
(
select ((unnest(feature_name)).name) as name
) s) as names
from meta_contact_details
GROUP BY id
效果:
=======================以下为修改内容==================
使用上述sql在不行查id字段的时候会报错
这里两种方式,一种是去掉group by
第二种是在group by 后将names字段加上
select guid,
(select string_agg(name, ',') from
(
select ((unnest(feature_name)).name) as name
) s) as names
from meta_contact_details
select guid,
(select string_agg(name, ',') from
(
select ((unnest(feature_name)).name) as name
) s) as names
from meta_contact_details
GROUP BY guid,names