直接上sql
- SQL code
create 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}
)
也就是如下：
- SQL code
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,
sum( QUOTA), sum(PURCHASE)
from TEMP_TEST
GROUP BY GROUPING SETS((SALEMANAGER, DEPTNAME, VC_REGION, REGIONSCALE),(VC_REGION),(DEPTNAME))
------解决方案--------------------
select * from temp_test
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;