前端之家

  1. 首页
  2. 问答

如何在postgres中将多个行值展平为串联的字符串？

• 问答

我有一组简单的三个表。 2个“源”表和一个允许一对多关系的“联接”表。

我有这个查询：

          select data.mph1.data ->> 'name' as name,data.mph1.data ->> 'tags' as Tags,data.mph2.data ->> 'School' as school from data.mph1 
          join data.mph1tomph2 on data.mph1tomph2.mph1 = data.mph1.id
          join data.mph2 on data.mph2.id = data.mph1tomph2.mph2

输出显示为：

            Name             Tags                                School
           "Steve Jones"    "["tag1","tag2"]"                  "UMass"
           "Steve Jones"    "["tag1","tag2"]"                  "Harvard"
           "Gary Summers"   "["java","postgres","flutter"]"   "Yale"
           "Gary Summers"   "["java","flutter"]"   "Harvard"
           "Gary Summers"   "["java","flutter"]"   "UMass"

我正在寻找的是

            Name             Tags                               School
           "Steve Jones"    "["tag1","tag2"]"                  "UMass","Harvard"
           "Gary Summers"   "["java","flutter"]"   "Yale,Harvard,UMass"

如何在单个查询中得到此结果？可能吗？

aiaiai5 回答：如何在postgres中将多个行值展平为串联的字符串？

使用聚合函数string_agg() 

select 
    data.mph1.data ->> 'name' as name,data.mph1.data ->> 'tags' as tags,string_agg(data.mph2.data ->> 'School',',') as school
from data.mph1 
join data.mph1tomph2 on data.mph1tomph2.mph1 = data.mph1.id
join data.mph2 on data.mph2.id = data.mph1tomph2.mph2
group by data.mph1.id  -- if id is a primary key
-- group by 1,2       -- otherwise
postgresqlpostgresql-9.5
本文链接：https://www.f2er.com/3164451.html

大家都在问