我有5个表，其中包含如下行：
研究报告表：
REPORT_ID TOPIC
141 My Report Topic Title
142 Another Report Topic Title
143 Yet Another Report Topic Title
...程序区域报告关系表：
REPORT_ID PROGRAM_AREA_ID
141 6
141 11
141 12
...程序区域表：
PROGRAM_AREA_ID TITLE
6 Program Area One
11 Program Area Two
12 Program Area Three
...研究报告分类关系表：
REPORT_ID CATEGORY_ID
141 9
141 10
141 18
141 23
...研究报告分类表：
CATEGORY_ID NAME
9 Category One
10 Category Two
18 Category Three
23 Category Four
...此查询当前正在从下面返回结果：
SELECT rr.report_id,
rr.topic,
string_agg(pa.title, '|') as program_areas,
string_agg(rrc.name, '|') as categories
FROM RESEARCH_REPORTS rr
LEFT JOIN PROGRAM_AREAS_REPORTS_REL parr ON rr.report_id = parr.report_id
LEFT JOIN RESEARCH_REPORT_CATEGORY_REL rrcr ON rr.report_id = rrcr.report_id
LEFT JOIN PROGRAM_AREAS pa ON parr.program_area_id = pa.program_area_id
LEFT JOIN RESEARCH_REPORT_CATEGORIES rrc ON rrcr.category_id = rrc.category_id
WHERE rr.report_id = 141
GROUP BY rr.report_id, rr.topic查询结果：
|---------------------|-------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| report_id | topic | program_areas | categories |
|---------------------|-------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 141 | My Report Topic Title | Program Area One|Program Area Two|Program Area Three|Program Area One|Program Area Two|Program Area Three|Program Area One|Program Area Two|Program Area Three | Category One|Category Two|Category Three|Category Four|Category One|Category Two|Category Three|Category Four|Category One|Category Two|Category Three|Category Four|Category One|Category Two|Category Three|Category Four |
|---------------------|-------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|如果注意到，结果列中的聚合值 program_areas 以及 categories 都是重复的。我需要使用select查询避免这些重复，该查询的结果格式如下：
首选查询结果：
|---------------------|-------------------------------------|---------------------------------------------------------|--------------------------------------------------------------|
| report_id | topic | program_areas | categories |
|---------------------|-------------------------------------|---------------------------------------------------------|--------------------------------------------------------------|
| 141 | My Report Topic Title | Program Area One|Program Area Two|Program Area Three | Category One|Category Two|Category Three|Category Four |
|---------------------|-------------------------------------|---------------------------------------------------------|--------------------------------------------------------------|如何在当前查询中完成此操作？
2条答案
按热度按时间crcmnpdw1#
你可以试试这个
但我不知道如何设置由字符串\u agg连接的名称/标题的顺序。
z3yyvxxp2#
尝试此查询