GROUP BY GROUPING SETS 示例

--
建表
create table TEst1
(
  ID        VARCHAR2(2),
  co_CODE    VARCHAR2(10),
  T_NAME    VARCHAR2(10),
  Money  INTEGER,
  P_code    VARCHAR2(10)
);

--插入基础数据

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('1', 'YB0101', '单位1', 50, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('2', 'YB0101', '单位1', 20, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('3', 'YB0101', '单位1', 30, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('4', 'YB0102', '单位2', 10, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('5', 'YB0102', '单位2', 20, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('6', 'YB01', '单位', 15, null);

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)
values ('7', 'YB01', '单位', 25, null);

--------------------------
--测试 GROUP BY GROUPING SETS
SELECT *
  FROM (SELECT ID, CO_CODE, T_NAME, MONEY, P_CODE, SM, SUM(SM) SSM
          FROM (SELECT ID,
                       (CASE
                         WHEN CO_CODE IS NULL THEN
                          P_CODE
                         ELSE
                          CO_CODE
                       END) CO_CODE,
                       T_NAME,
                       MONEY,
                       P_CODE,
                       SM
                  FROM (SELECT ID,
                               CO_CODE,
                               T_NAME,
                               MONEY,
                               P_CODE,
                               SUM(MONEY) SM
                          FROM TEST1
                         GROUP BY GROUPING SETS((ID, CO_CODE, T_NAME, MONEY, P_CODE),(P_CODE)))
                 WHERE (P_CODE IS NOT NULL OR CO_CODE IS NOT NULL))
         GROUP BY GROUPING SETS((CO_CODE, ID, T_NAME, MONEY, P_CODE, SM), CO_CODE)
         ORDER BY CO_CODE, ID NULLS FIRST)
 WHERE (P_CODE IS NULL OR
       (CO_CODE IS NOT NULL AND P_CODE IS NOT NULL AND CO_CODE <> P_CODE))

------------------------

原始表：

处理之后结果：