sql - 从逗号分隔列表中的联接表中获取多个列值
问题描述
我有 3 个表,其中包含如下行:
RESEARCH_REPORTS表:
REPORT_ID TOPIC
141 My Report Topic Title
142 Another Report Topic Title
143 Yet Another Report Topic Title
...
PROGRAM_AREAS_REPORTS_REL表:
REPORT_ID PROGRAM_AREA_ID
141 6
141 11
141 12
...
PROGRAM_AREAS表:
PROGRAM_AREA_ID TITLE
6 Program Area One
11 Program Area Two
12 Program Area Three
...
此查询目前正在向我返回下面的结果:
SELECT rr.report_id, rr.topic, pa.title as program_areas
FROM RESEARCH_REPORTS rr
INNER JOIN PROGRAM_AREAS_REPORTS_REL parr ON rr.report_id = parr.report_id
INNER JOIN PROGRAM_AREAS pa ON parr.program_area_id = pa.program_area_id
WHERE rr.report_id = 141
查询结果:
report_id topic program_areas
141 My Report Topic Title Program Area One
141 My Report Topic Title Program Area Two
141 My Report Topic Title Program Area Three
我需要一个选择查询,它会产生以下格式:
首选查询结果:
report_id topic program_areas
141 My Report Topic Title Program Area One, Program Area Two, Program Area Three
如何在当前查询中完成此操作?
解决方案
使用string_agg()
功能:-
SELECT rr.report_id, rr.topic, string_agg(pa.title,',') as program_areas
FROM RESEARCH_REPORTS rr
INNER JOIN PROGRAM_AREAS_REPORTS_REL parr ON rr.report_id = parr.report_id
INNER JOIN PROGRAM_AREAS pa ON parr.program_area_id = pa.program_area_id
WHERE rr.report_id = 141
group by rr.report_id, rr.topic
推荐阅读
- r - 在 R 中生成给定百分比的删失数据
- python - 如何根据熊猫中的列值生成字符串
- c# - CsvHelper : 写作清单
转为 CSV - mongodb - 从集合中获取 N 个最新的不同记录 [Mongodb]
- c# - 无法使用 ASP.NET Core 标识从基于角色的 HttpGet 请求中列出数据
- ansible - 在 Ansible 中,如何将现有行移动到文件末尾?
- css - 有没有办法对颜色和 SVG 进行分层以制作背景
- assembly - GCC 编译为程序集:cmp 后跟调用而不是条件跳转
- excel - Excel Power Pivot - 重叠的日期范围
- mysql - 无法分析 OSSEC 中的 MySQL 错误日志