如何在Oracle SQL Developer中按组织名合并多SELECT查询结果

你可以通过以下两种方式实现按Name和ALL_SCORES合并结果的需求：

方案一：子查询关联（LEFT JOIN） #

将每个查询作为独立子查询，通过Name和ALL_SCORES字段进行左连接，确保所有组织的记录都能被保留，无对应数据的列会显示NULL：

SELECT
    base.Name,
    base.ALL_SCORES,
    pas.Pasmaand,
    ham.Hamlonaghl,
    sha.Shahrvand
FROM
    (SELECT Name, ALL_SCORES FROM ORG_ALL_SCORES) base
LEFT JOIN
    (SELECT
         ORG_ALL_SCORES.Name,
         ORG_ALL_SCORES.ALL_SCORES,
         SUM(REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE) AS Pasmaand
     FROM ORG_ALL_SCORES
     LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID = ORG_ALL_SCORES.ORGANIZATION_ID
     INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID
     WHERE REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700022%'
     GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES) pas
    ON base.Name = pas.Name AND base.ALL_SCORES = pas.ALL_SCORES
LEFT JOIN
    (SELECT
         ORG_ALL_SCORES.Name,
         ORG_ALL_SCORES.ALL_SCORES,
         SUM(REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE) AS Hamlonaghl
     FROM ORG_ALL_SCORES
     LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID = ORG_ALL_SCORES.ORGANIZATION_ID
     INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID
     WHERE REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700041%'
     GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES) ham
    ON base.Name = ham.Name AND base.ALL_SCORES = ham.ALL_SCORES
LEFT JOIN
    (SELECT
         ORG_ALL_SCORES.Name,
         ORG_ALL_SCORES.ALL_SCORES,
         SUM(REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE) AS Shahrvand
     FROM ORG_ALL_SCORES
     LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID = ORG_ALL_SCORES.ORGANIZATION_ID
     INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID
     WHERE REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700061%'
     GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES) sha
    ON base.Name = sha.Name AND base.ALL_SCORES = sha.ALL_SCORES;

方案二：条件聚合（更高效） #

利用CASE WHEN在聚合函数中做条件判断，只需一次表扫描，性能优于子查询关联，逻辑更简洁：

SELECT
    ORG_ALL_SCORES.Name,
    ORG_ALL_SCORES.ALL_SCORES,
    SUM(CASE WHEN REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700022%' THEN REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE ELSE 0 END) AS Pasmaand,
    SUM(CASE WHEN REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700041%' THEN REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE ELSE 0 END) AS Hamlonaghl,
    SUM(CASE WHEN REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700061%' THEN REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE ELSE 0 END) AS Shahrvand
FROM ORG_ALL_SCORES
LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID = ORG_ALL_SCORES.ORGANIZATION_ID
INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID
WHERE
    REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700022%'
    OR REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700041%'
    OR REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700061%'
GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES;

注意事项 #

• 如果需要保留没有匹配到任何REWARD_CONVERSION_FORMULA记录的组织，需要将INNER JOIN REWARD_CONVERSION_FORMULA改为LEFT JOIN REWARD_CONVERSION_FORMULA，同时把SUM中的ELSE 0改为ELSE NULL（或去掉ELSE，默认返回NULL），并且去掉WHERE条件。
• 条件聚合方案在数据量较大时性能优势明显，推荐优先使用。

内容的提问来源于stack exchange，提问作者MonaK