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 ('', 'YB0101', '单位1', 50, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)

values ('', 'YB0101', '单位1', 20, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)

values ('', 'YB0101', '单位1', 30, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)

values ('', 'YB0102', '单位2', 10, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)

values ('', 'YB0102', '单位2', 20, 'YB01');

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)

values ('', 'YB01', '单位', 15, null);

insert into TEST1 (ID, CO_CODE, T_NAME, MONEY, P_CODE)

values ('', '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))

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

原始表：

处理之后结果：