postgresql - 如何在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", "postgres", "flutter"]" "Harvard"
"Gary Summers" "["java", "postgres", "flutter"]" "UMass"
我正在寻找的是
Name Tags School
"Steve Jones" "["tag1", "tag2"]" "UMass", "Harvard"
"Gary Summers" "["java", "postgres", "flutter"]" "Yale, Harvard, UMass"
如何在单个查询中获得此结果?可能的?
解决方案
使用聚合函数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
推荐阅读
- javascript - 修改js中对象数组里面的对象数组
- reactjs - 反应原生 ScrollView 不滚动而没有任何错误
- android - 尽管正确调用了observeOn(io线程),但RxJava NetworkOnMainThreadException
- kotlin - Kotlin 使用另一个对象作为默认构造函数参数创建新对象
- c++ - 在 g++ 编译器上运行 C++ 程序时 VSCode 显示错误
- reactjs - 如何在我的 React 应用程序中创建一个单独的动态窗口?
- ios - RTCClient :您必须先登录才能使用聊天 API
- google-colaboratory - 如何在 Google Colab 中使用 TreeTagger?
- build - Spring Boot 和 React 应用程序的生产构建问题
- php - 以编程方式编辑 WooCommerce 应税运费时包括税费