直接上sqlcreate table TEMP_TEST
(
salemanager VARCHAR2(50),
deptname VARCHAR2(50),
vc_region VARCHAR2(50),
regionscale NUMBER,
quota NUMBER,
purchase NUMBER
);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('赵六', '销售部A', '安徽', 1, 10000, 1568);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('张三', '销售部B', '北京', 0.6, 5000, 1098);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('李四', '销售部A', '广西', 0.5, 5000, 8850);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('王五', '销售部A', '广西', 0.5, 5000, 7500);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('钱七', '销售部B', '北京', 0.4, 5000, 1146);
commit;
结果满足
(
1. 各人、各部门、地区 的 {t.regionscale,t.quota,t.purchase}
2. 各地区 的 {t.salemanager, t.quota,t.purchase}
3.各部门的 {t.quota,t.purchase}
)也就是如下:
SALEMANAGER DEPTNAME VC_REGION REGIONSCALE QUOTA PURCHASE
赵六 销售部A 安徽 1 10000 1568
张三 销售部B 北京 0.6 5000 1098
钱七 销售部B 北京 0.4 5000 1146
李四 销售部A 广西 0.5 5000 8850
王五 销售部A 广西 0.5 5000 7500 赵六 安徽 1000 1568
张三,钱七 北京 10000 2244
李四,王五 广西 10000 16350
销售部A 20000 18168
销售部B 10000 2244
(
salemanager VARCHAR2(50),
deptname VARCHAR2(50),
vc_region VARCHAR2(50),
regionscale NUMBER,
quota NUMBER,
purchase NUMBER
);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('赵六', '销售部A', '安徽', 1, 10000, 1568);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('张三', '销售部B', '北京', 0.6, 5000, 1098);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('李四', '销售部A', '广西', 0.5, 5000, 8850);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('王五', '销售部A', '广西', 0.5, 5000, 7500);
insert into TEMP_TEST (SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE)
values ('钱七', '销售部B', '北京', 0.4, 5000, 1146);
commit;
结果满足
(
1. 各人、各部门、地区 的 {t.regionscale,t.quota,t.purchase}
2. 各地区 的 {t.salemanager, t.quota,t.purchase}
3.各部门的 {t.quota,t.purchase}
)也就是如下:
SALEMANAGER DEPTNAME VC_REGION REGIONSCALE QUOTA PURCHASE
赵六 销售部A 安徽 1 10000 1568
张三 销售部B 北京 0.6 5000 1098
钱七 销售部B 北京 0.4 5000 1146
李四 销售部A 广西 0.5 5000 8850
王五 销售部A 广西 0.5 5000 7500 赵六 安徽 1000 1568
张三,钱七 北京 10000 2244
李四,王五 广西 10000 16350
销售部A 20000 18168
销售部B 10000 2244
期待大神,
写一个函数能实现中间段的拼接。不过就做不到一句语句了
select SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE, QUOTA, PURCHASE from TEMP_TEST
union all
select '' SALEMANAGER, '' DEPTNAME, VC_REGION, sum(REGIONSCALE) REGIONSCALE , sum(QUOTA) QUOTA, sum(PURCHASE) PURCHASE
from TEMP_TEST
group by VC_REGION
union all
select '' SALEMANAGER, DEPTNAME, '' VC_REGION, sum(REGIONSCALE) REGIONSCALE , sum(QUOTA) QUOTA, sum(PURCHASE) PURCHASE
from TEMP_TEST
group by DEPTNAME
SALEMANAGER DEPTNAME VC_REGION REGIONSCALE QUOTA PURCHASE
赵六 销售部A 安徽 1 10000 1568
张三 销售部B 北京 0.6 5000 1098
钱七 销售部B 北京 0.4 5000 1146
李四 销售部A 广西 0.5 5000 8850
王五 销售部A 广西 0.5 5000 7500 安徽 1000 1568
北京 10000 2244
广西 10000 16350
销售部A 20000 18168
销售部B 10000 2244
DEPTNAME,
VC_REGION,
REGIONSCALE,
sum( QUOTA), sum(PURCHASE)
from TEMP_TEST
GROUP BY GROUPING SETS((SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE),(VC_REGION),(DEPTNAME))
DEPTNAME,
VC_REGION,
REGIONSCALE,
sum( QUOTA), sum(PURCHASE)
from TEMP_TEST
GROUP BY GROUPING SETS((SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE),(VC_REGION),(DEPTNAME))
union all
select wmsys.wm_concat(tt.salemanager) salemanager,
null as deptname,
tt.vc_region,
null as regionscale,
sum(tt.quota) quota,
sum(tt.purchase) purchase
from temp_test tt
group by tt.vc_region
union all
select null as salemanager,
tt.deptname,
null as vc_region,
null as regionscale,
sum(tt.quota) quota,
sum(tt.purchase) purchase
from temp_test tt
group by tt.deptname;
如上面的 :PURCHASE 是由 REGIONSCALE * PURCHASE 得到的! (能理解吗)
这样下来 GROUP BY GROUPING SETS 岂不是用不上了?
或者GROUP BY CUBE(field1,field2)结合使用GROUPING(FIELD1)
可以一句话实现你要的效果。