SQL代码行与列求和及报表缺失字段补全需求问询

看你的需求是要给报表加上行合计、列合计，还要完善缺失的字段结构对吧？咱们可以通过Oracle的GROUPING SETS来实现多级合计，同时调整字段计算逻辑，刚好匹配你期望的报表效果，具体修改如下：

修改后的SQL代码 #

WITH PLAN_CODES AS (
    SELECT DISTINCT
        test.MBR_COV.MBR_ID,
        TRUNC(MONTHS_BETWEEN(SYSDATE, test.MBR.BTH_DT)/12) AS AGE,
        FLOOR(MONTHS_BETWEEN(TO_DATE('03/31/2018','MM/DD/YYYY'), test.MBR.BTH_DT)/12) AS AGE_ON_REPORTING_DATE,
        CASE
            WHEN test.MBR_COV.PLN_VAR_ID LIKE 'TE%' THEN 'EAST'
            WHEN test.MBR_COV.PLN_VAR_ID LIKE 'TW%' THEN 'WEST'
            WHEN test.MBR_COV.PLN_VAR_ID LIKE 'TM%' THEN 'MIDDLE'
            ELSE 'N/A'
        END AS REGION,
        CASE
            WHEN SUBSTR(test.MBR_COV.LGCY_BEN_PLN_ID,1,4) = 'TNC4' THEN 'GROUP 4'
            WHEN SUBSTR(test.MBR_COV.LGCY_BEN_PLN_ID,1,4) = 'TNC5' THEN 'GROUP 5'
            WHEN SUBSTR(test.MBR_COV.LGCY_BEN_PLN_ID,1,4) = 'TNC6' THEN 'GROUP 6'
            WHEN SUBSTR(test.MBR_COV.LGCY_BEN_PLN_ID,1,4) = 'TNC7' THEN 'GROUP 7'
            WHEN SUBSTR(test.MBR_COV.LGCY_BEN_PLN_ID,1,4) = 'TNC8' THEN 'GROUP 8'
            ELSE 'N/A'
        END AS CHOICES_GROUP
    FROM test.MBR_COV
    INNER JOIN test.MBR ON test.MBR_COV.MBR_ID = test.MBR.MBR_ID
),
AGE_GROUPED AS (
    SELECT
        CHOICES_GROUP,
        CASE
            WHEN AGE_ON_REPORTING_DATE BETWEEN 16 AND 18 THEN 'WORKING_AGE_MEMBERS_16_18'
            WHEN AGE_ON_REPORTING_DATE BETWEEN 19 AND 21 THEN 'WORKING_AGE_MEMBERS_19_21'
            WHEN AGE_ON_REPORTING_DATE BETWEEN 22 AND 25 THEN 'WORKING_AGE_MEMBERS_22_25'
            WHEN AGE_ON_REPORTING_DATE BETWEEN 26 AND 34 THEN 'WORKING_AGE_MEMBERS_26_34'
            WHEN AGE_ON_REPORTING_DATE BETWEEN 35 AND 46 THEN 'WORKING_AGE_MEMBERS_35_46'
            WHEN AGE_ON_REPORTING_DATE BETWEEN 47 AND 62 THEN 'WORKING_AGE_MEMBERS_47_62'
        END AS AGE_GROUP,
        REGION
    FROM PLAN_CODES
    WHERE
        AGE_ON_REPORTING_DATE BETWEEN 16 AND 62
        AND REGION IN ('EAST','MIDDLE','WEST')
        AND CHOICES_GROUP IN ('GROUP 4','GROUP 5','GROUP 6','GROUP 7','GROUP 8')
)
SELECT
    CASE
        WHEN GROUPING(CHOICES_GROUP) = 1 THEN 'TOTAL'
        ELSE CHOICES_GROUP
    END AS CHOICES_GROUP,
    CASE
        WHEN GROUPING(AGE_GROUP) = 1 THEN 'GROUP TOTAL'
        ELSE AGE_GROUP
    END AS AGE_GROUP,
    SUM(CASE WHEN REGION = 'EAST' THEN 1 ELSE 0 END) AS EAST,
    SUM(CASE WHEN REGION = 'WEST' THEN 1 ELSE 0 END) AS WEST,
    SUM(CASE WHEN REGION = 'MIDDLE' THEN 1 ELSE 0 END) AS MIDDLE,
    SUM(1) AS TOTAL -- 新增行合计字段
FROM AGE_GROUPED
GROUP BY GROUPING SETS(
    (CHOICES_GROUP, AGE_GROUP), -- 基础分组：每个组+年龄组的明细
    (CHOICES_GROUP), -- 组内合计：每个组的总人数
    () -- 全局合计：所有组的总人数
)
ORDER BY
    CASE WHEN CHOICES_GROUP = 'TOTAL' THEN 2 ELSE 1 END, -- 把全局合计行放最后
    CHOICES_GROUP,
    CASE WHEN AGE_GROUP = 'GROUP TOTAL' THEN 2 ELSE 1 END; -- 把组内合计放每个组的末尾

关键修改点说明 #

• 拆分逻辑成子查询：把年龄分组的CASE逻辑单独放到AGE_GROUPED里，避免主查询重复写相同代码，可读性更强。
• 用GROUPING SETS实现多级合计：
  • (CHOICES_GROUP, AGE_GROUP)：生成每个分组下各年龄组的明细数据
  • (CHOICES_GROUP)：自动生成每个组的行合计（对应GROUP TOTAL行）
  • ()：自动生成所有组的全局合计（对应TOTAL行）
• 新增TOTAL列：通过SUM(1)计算每个分组的总人数，实现行合计功能。
• 用GROUPING()识别合计行：判断当前行是明细还是合计，从而显示对应的文本标识，让报表更清晰。
• 调整排序规则：确保合计行显示在正确的位置，和你期望的报表结构完全匹配。

这个修改后的SQL会生成你想要的报表效果：每个分组下有各年龄组的明细、组内合计行，最后还有全局合计行，同时补上了缺失的行合计字段。

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