--建表
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))
------------------------
原始表：
处理之后结果：
转载于:https://www.cnblogs.com/fei-yang/p/3867208.html
相关资源：DirectX修复工具V4.0增强版
