时间戳

首页 > 解决方案 > SELECT 与多个 JOINS 包括 LISTAGG 与它自己的 JOIN

oracle - SELECT 与多个 JOINS 包括 LISTAGG 与它自己的 JOIN

问题描述

我有以下 2 个单独运行时可以完美运行的查询。

查询 1

SELECT eps.PROPOSAL_NUMBER, eps.TITLE, per.FULL_NAME, ext.NRP_IREF_CD, ext.RESEARCH_TYPE_CD, 
   nsf.NSF_CODE, ext.NPS_THRUST_DESCRIPTION,
   CASE
        WHEN OPTION_1 = 'Y' THEN 'Option 1'
        WHEN OPTION_2 = 'Y' THEN 'Option 2'
        WHEN OPTION_3 = 'Y' THEN 'Option 3'
        WHEN OPTION_4 = 'Y' THEN 'Option 4'
        WHEN OPTION_5 = 'Y' THEN 'Option 5'
        WHEN OPTION_6 = 'Y' THEN 'Option 6'
        WHEN OPTION_7 = 'Y' THEN 'Option 7'
        WHEN OPTION_8 = 'Y' THEN 'Option 8'
        WHEN OPTION_9 = 'Y' THEN 'Option 9' 
        WHEN OPTION_0 = 'Y' THEN 'Not Applicable'
        ELSE ''
   END ONR,       
   abs.ABSTRACT_DETAILS
FROM EPS_PROPOSAL eps
LEFT JOIN EPS_PROPOSAL_EXT_T ext
    ON eps.PROPOSAL_NUMBER = ext.PROPOSAL_NUMBER
LEFT JOIN EPS_PROP_PERSON per
    ON eps.PROPOSAL_NUMBER = per.PROPOSAL_NUMBER AND
      (per.PROP_PERSON_ROLE_ID = 'PI' OR per.PROP_PERSON_ROLE_ID = 'PD')
LEFT JOIN EPS_PROP_ABSTRACT abs
    ON eps.PROPOSAL_NUMBER = abs.PROPOSAL_NUMBER
LEFT JOIN NSF_CODES nsf
    ON eps.NSF_CODE = nsf.NSF_SEQUENCE_NUMBER    
WHERE eps.OWNED_BY_UNIT = '401' AND eps.requested_start_date_initial >= DATE '2019-10-01';

结果

--------
PROPOSAL_NUMBER    TITLE    FULL_NAME    NRP_IREF_CD ... <br />
     2028          blah       ...           ....         <br />
     2029          blah2      ...           ....         <br />
     2030          blah3      ...           ....         <br />

查询 2

SELECT eps.PROPOSAL_NUMBER,
   LISTAGG(sk.DESCRIPTION, ', ') WITHIN GROUP (ORDER BY sk.DESCRIPTION) AS KEYWORDS 
FROM EPS_PROPOSAL eps
LEFT JOIN EPS_PROP_SCIENCE_KEYWORD key 
    ON eps.PROPOSAL_NUMBER = key.PROPOSAL_NUMBER
LEFT JOIN SCIENCE_KEYWORD sk
    ON key.SCIENCE_KEYWORD_CODE = sk.SCIENCE_KEYWORD_CODE
GROUP BY eps.PROPOSAL_NUMBER;

结果

--------
PROPOSAL_NUMBER    KEYWORDS
     2028          Keyword_A, Keyword_B, Keyword_C, Keyword_D
     2029          Keyword_Y, Keyword_Z
     2030          Keyword_W

所以你可以看到我正在处理的主表是EPS_PROPOSAL和那里的 PK PROPOSAL_NUMBER。还有其他表将该 PK 用作 FK，例如EPS_PROP_SCIENCE_KEYWORD，您可以在其中拥有多行 KEYWORDS 映射到相同的 PROPOSAL_NUMBER。问题是只有 ID 或代码存储在该表上，因此需要与SCIENCE_KEYWORD表 JOIN 以获取描述。

问题：如何组合这两个查询，以便我的结果如下所示：

Results
--------
PROPOSAL_NUMBER    TITLE    FULL_NAME    NRP_IREF_CD ...  KEYWORDS
     2028          blah       ...           ....           Keyword_A, Keyword_B, Keyword_C, Keyword_D
     2029          blah2      ...           ....           Keyword_Y, Keyword_Z
     2030          blah3      ...           ....           Keyword_W

标签： oraclegroup-byleft-joinlistagg

解决方案

最简单的方法是进行小修改 - 使用子查询添加 OUTER APPLY：

SELECT eps.PROPOSAL_NUMBER, eps.TITLE, per.FULL_NAME, ext.NRP_IREF_CD, ext.RESEARCH_TYPE_CD, 
   nsf.NSF_CODE, ext.NPS_THRUST_DESCRIPTION,
   CASE
        WHEN OPTION_1 = 'Y' THEN 'Option 1'
        WHEN OPTION_2 = 'Y' THEN 'Option 2'
        WHEN OPTION_3 = 'Y' THEN 'Option 3'
        WHEN OPTION_4 = 'Y' THEN 'Option 4'
        WHEN OPTION_5 = 'Y' THEN 'Option 5'
        WHEN OPTION_6 = 'Y' THEN 'Option 6'
        WHEN OPTION_7 = 'Y' THEN 'Option 7'
        WHEN OPTION_8 = 'Y' THEN 'Option 8'
        WHEN OPTION_9 = 'Y' THEN 'Option 9' 
        WHEN OPTION_0 = 'Y' THEN 'Not Applicable'
        ELSE ''
   END ONR,       
   abs.ABSTRACT_DETAILS,
   k.keywords
FROM EPS_PROPOSAL eps
LEFT JOIN EPS_PROPOSAL_EXT_T ext
    ON eps.PROPOSAL_NUMBER = ext.PROPOSAL_NUMBER
LEFT JOIN EPS_PROP_PERSON per
    ON eps.PROPOSAL_NUMBER = per.PROPOSAL_NUMBER AND
      (per.PROP_PERSON_ROLE_ID = 'PI' OR per.PROP_PERSON_ROLE_ID = 'PD')
LEFT JOIN EPS_PROP_ABSTRACT abs
    ON eps.PROPOSAL_NUMBER = abs.PROPOSAL_NUMBER
LEFT JOIN NSF_CODES nsf
    ON eps.NSF_CODE = nsf.NSF_SEQUENCE_NUMBER    
OUTER APPLY(
   SELECT 
      LISTAGG(sk.DESCRIPTION, ', ') WITHIN GROUP (ORDER BY sk.DESCRIPTION) AS KEYWORDS 
   FROM EPS_PROP_SCIENCE_KEYWORD key 
   LEFT JOIN SCIENCE_KEYWORD sk
       ON key.SCIENCE_KEYWORD_CODE = sk.SCIENCE_KEYWORD_CODE
   WHERE eps.PROPOSAL_NUMBER = key.PROPOSAL_NUMBER
) k
WHERE eps.OWNED_BY_UNIT = '401' AND eps.requested_start_date_initial >= DATE '2019-10-01';

推荐阅读