SQL 关联表分组统计（多条合并一条）

Windyº

已于 2023-03-21 11:52:42 修改

阅读量1.2k

点赞数

分类专栏：积沙成塔文章标签： sql 数据库

于 2023-01-11 16:59:26 首次发布

本文链接：https://blog.csdn.net/weixin_42096792/article/details/128647603

版权

积沙成塔专栏收录该内容

40 篇文章 4 订阅

订阅专栏

需求是：报表中需要展示统计的数据。根据组织分组查询不同类型的数据，统计出分组后的值。
在这里插入图片描述

先统计出来是这个样子：
在这里插入图片描述

因为是多表关联，分组后张三出现了3次，其中两次后面的值都是NULL，每个只有一行有行数还都不一样。

怎么统计？分组统计后，当成表在分组一次，每个值用SUM()函数接收。因为 NULL+数值的SUM()，NULL以0处理。

SELECT tmp.ORG_NAME        AS ORG_NAME,
       NVL(SUM(tmp.C1), 0) AS BGF1,
       NVL(SUM(tmp.C2), 0) AS CLF1,
       NVL(SUM(tmp.C3), 0) AS ZDF1,
       NVL(SUM(tmp.C4), 0) AS HYF1,
       NVL(SUM(tmp.C5), 0) AS BGF2,
       NVL(SUM(tmp.C6), 0) AS CLF2,
       NVL(SUM(tmp.C7), 0) AS ZDF2,
       NVL(SUM(tmp.C8), 0) AS HYF2
FROM (SELECT t.ORG_NAME,
             t1.AMOUNT AS C1,
             t2.AMOUNT AS C2,
             t3.AMOUNT AS C3,
             t4.AMOUNT AS C4,
             t5.AMOUNT AS C5,
             t6.AMOUNT AS C6,
             t7.AMOUNT AS C7,
             t8.AMOUNT AS C8
      FROM IAM_FINANCIAL_PARTY_BUILD_EXP t
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t1 ON t.ID = t1.ID AND t1.ACCOUNT = 'ZF86-1024' AND t1.YEAR = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 1) AND t1.PERIOD = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t2 ON t.ID = t2.ID AND t2.ACCOUNT = 'ZF86-1034' AND t2.YEAR = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 1) AND t2.PERIOD = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t3 ON t.ID = t3.ID AND t3.ACCOUNT = 'ZF86-1044' AND t3.YEAR = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 1) AND t3.PERIOD = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t4 ON t.ID = t4.ID AND t4.ACCOUNT = 'ZF86-1054' AND t4.YEAR = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 1) AND t4.PERIOD = REGEXP_SUBSTR('2022-05', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t5 ON t.ID = t5.ID AND t5.ACCOUNT = 'ZF86-1024' AND t5.YEAR = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 1) AND t5.PERIOD = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t6 ON t.ID = t6.ID AND t6.ACCOUNT = 'ZF86-1034' AND t6.YEAR = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 1) AND t6.PERIOD = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t7 ON t.ID = t7.ID AND t7.ACCOUNT = 'ZF86-1044' AND t7.YEAR = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 1) AND t7.PERIOD = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 2)
               LEFT JOIN IAM_FINANCIAL_PARTY_BUILD_EXP t8 ON t.ID = t8.ID AND t8.ACCOUNT = 'ZF86-1054' AND t8.YEAR = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 1) AND t8.PERIOD = REGEXP_SUBSTR('2022-09', '[^-]+', 1, 2)
      WHERE t.CHANGE_TYPE = 'YTD'
        AND t.ORG_NAME IN ('${ORG_NAME}')
      GROUP BY t.ORG_NAME, t1.AMOUNT, t2.AMOUNT, t3.AMOUNT, t4.AMOUNT, t5.AMOUNT, t6.AMOUNT, t7.AMOUNT, t8.AMOUNT
      ORDER BY t.ORG_NAME, t1.AMOUNT, t2.AMOUNT, t3.AMOUNT, t4.AMOUNT, t5.AMOUNT, t6.AMOUNT, t7.AMOUNT, t8.AMOUNT) tmp
GROUP BY tmp.ORG_NAME;