时间戳

首页 > 解决方案 > 避免重复来自 2 个左连接表的聚合列值

sql - 避免重复来自 2 个左连接表的聚合列值

问题描述

我有 5 个表，其中包含如下行：

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
...

RESEARCH_REPORT_CATEGORY_REL表：

REPORT_ID    CATEGORY_ID
141          9
141          10
141          18
141          23
...

RESEARCH_REPORT_CATEGORIES表：

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重复。我需要使用导致以下格式的选择查询来避免这些重复：

首选查询结果：

|---------------------|-------------------------------------|---------------------------------------------------------|--------------------------------------------------------------|
|      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    |
|---------------------|-------------------------------------|---------------------------------------------------------|--------------------------------------------------------------|

如何在当前查询中完成此操作？

标签： sqlsql-serversql-server-2017

解决方案

你可以试试这个

SELECT  rr.report_id
        , rr.topic
        , ( SELECT  string_agg(pa.title, '|')
            FROM    PROGRAM_AREAS pa
            JOIN    PROGRAM_AREAS_REPORTS_REL parr
            ON      parr.program_area_id = pa.program_area_id 
            WHERE   parr.report_id = rr.report_id
            GROUP BY parr.report_id ) as program_areas
        , ( SELECT  string_agg(rrc.name, '|')
            FROM    RESEARCH_REPORT_CATEGORIES rrc 
            JOIN    RESEARCH_REPORT_CATEGORY_REL rrcr
            ON      rrcr.category_id = rrc.category_id 
            WHERE   rrcr.report_id = rr.report_id
            GROUP BY rrcr.report_id ) as categories
FROM    RESEARCH_REPORTS rr 
WHERE   rr.report_id = 141

我不确定如何设置由 STRING_AGG 连接的名称/标题的顺序。

推荐阅读