Oracle SQL如何合并两个关联聚合查询结果输出多列统计值

实现方案 #

你可以通过两种Oracle SQL方式完成需求，优先推荐第一种性能更高的写法：

方案1：单次关联多表直接聚合（推荐）

无需分别执行两次统计再合并，仅扫描一次BU表即可同时统计两个指标，避免重复计算：

SELECT
    T_BU.BU_CODE,
    COUNT(DISTINCT T_ACC.ACCOUNT_ID) AS COUNT_OF_ACCOUNT,
    COUNT(DISTINCT T_CON.CONTRACT_ID) AS COUNT_OF_CONTRACT
FROM BU T_BU -- 若实际表名为S_BU请替换此处表名
LEFT JOIN STG_ACCOUNT T_ACC ON T_BU.BU_CODE = T_ACC.BU_CODE
LEFT JOIN STG_CONTRACT T_CON ON T_BU.BU_CODE = T_CON.BU_CODE
WHERE T_BU.ARCHIVE_FLG = 'Y' -- 你原查询中写的过滤值为'1'，请根据ARCHIVE_FLG实际存储值调整
GROUP BY T_BU.BU_CODE
ORDER BY T_BU.BU_CODE;

说明：使用COUNT(DISTINCT)是为了避免账户表和合同表一对多关联时，数据重复导致计数错误。

方案2：关联两个现有查询的结果

如果你必须复用已有的两个查询语句，可以通过FULL OUTER JOIN关联两个子查询的结果，用NVL处理空值：

SELECT
    NVL(T_ACC_STAT.BU_CODE, T_CON_STAT.BU_CODE) AS BU_CODE,
    NVL(T_ACC_STAT.COUNT_OF_ACCOUNT, 0) AS COUNT_OF_ACCOUNT,
    NVL(T_CON_STAT.COUNT_OF_CONTRACT, 0) AS COUNT_OF_CONTRACT
FROM (
    -- 你的原有账户统计查询，去掉冗余的ORDER BY
    SELECT
    T2.BU_CODE, COUNT(T1.ACCOUNT_ID) AS COUNT_OF_ACCOUNT
    FROM STG_ACCOUNT T1
    FULL JOIN BU T2 ON T2.BU_CODE = T1.BU_CODE
    WHERE T2.ARCHIVE_FLG = 'Y'
    GROUP BY T2.BU_CODE
) T_ACC_STAT
FULL OUTER JOIN (
    -- 你的原有合同统计查询，去掉冗余的ORDER BY
    SELECT
    T2.BU_CODE, COUNT(T1.CONTRACT_ID) AS COUNT_OF_CONTRACT
    FROM STG_CONTRACT T1
    FULL JOIN BU T2 ON T2.BU_CODE = T1.BU_CODE
    WHERE T2.ARCHIVE_FLG = 'Y'
    GROUP BY T2.BU_CODE
) T_CON_STAT ON T_ACC_STAT.BU_CODE = T_CON_STAT.BU_CODE
ORDER BY BU_CODE;

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