日期:2014-05-17  浏览次数:20875 次

高手,这个查询结果你可以用一个sql得到吗?
直接上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;