[每日一题] OCP1z0-047 :2013-07-22 group by子句(二)
g sum(sal) > 4200 group by job
查询结果是一样的，具体这俩句有区别吗？？
没有区别，oracle having可以放在前后，常规写法第1种
三、理解分组
http://www.2cto.com/database/201307/229663.
html
如何实现比较复杂的分组、小计与合计
--测试代码
create table t_dist
(
TYPE_CD NUMBER,
BUYER_ID VARCHAR2(50),
ORDER_DT DATE,
SO_ID VARCHAR2(50) not null,
STOCK_ID VARCHAR2(50) not null,
UNIT_PRICE NUMBER,
DISCOUNT NUMBER,
QTY NUMBER
);
truncate table t_dist;
insert into t_dist values(1,'CN1001',to_date('2008-04-01','yyyy-mm-dd'),'S9001','29110311',50,10,8);
insert into t_dist values(1,'CN1001',to_date('2008-04-02','yyyy-mm-dd'),'S9002','29110312',60,20,2);
insert into t_dist values(1,'CN1001',to_date('2008-04-03','yyyy-mm-dd'),'S9003','29110313',70,15,3);
insert into t_dist values(2,'CN1001',to_date('2008-04-04','yyyy-mm-dd'),'S9004','29110312',60,15,5);
insert into t_dist values(2,'CN1001',to_date('2008-04-05','yyyy-mm-dd'),'S9005','29110311',70,10,6);
insert into t_dist values(3,'CN1001',to_date('2008-04-06','yyyy-mm-dd'),'S9006','29110313',55,20,4);
insert into t_dist values(3,'CN1001',to_date('2008-04-06','yyyy-mm-dd'),'S9007','29110311',40,10,3);
insert into t_dist values(3,'CN1001',to_date('2008-04-07','yyyy-mm-dd'),'S9008','29110312',50,50,5);
insert into t_dist values(3,'CN1001',to_date('2008-04-07','yyyy-mm-dd'),'S9009','29110313',80,10,2);
insert into t_dist values(1,'CN1001',to_date('2008-04-08','yyyy-mm-dd'),'S9010','29110311',65,10,1);
commit;
请问:如何实现如下结果,谢谢!
即计算按stock_id,type_cd,distount分组,计算每个产品的销售额(qty*unit_price)及销售百分比,并有小计
STOCK_ID TYPE_CD DISCOUNT AVG_PRICE SUM_TOT PCT
-------------------------------------------------------------------------------------------------
29110311 1 10 57.50 465 46.27%
29110311 2 10 70.00 420 41.79%
29110311 3 10 40.00 120 11.94%
小计 55.83 1005 100.00%
29110312 1 20 60.00 120 17.91%
29110312 2 15 60.00 300 44.78%
29110312 3 50 50.00 250 37.31%
小计 56.67 670 100.00%
29110313 1 15 70.00 210 35.59%
29110313 3 10 80.00 160 27.12%
29110313 3 20 55.00 220 37.29%
小计 68.33 590 100.00%
哦， 我明白了PCT的意思了， 以后要把问题描述的清楚点！[有没有奖励啊？ ]
具体如下：
SQL> select case when grouping_id(type_cd,discount) = 3 then '小计' else stock_id end stock_id,
2 type_cd,discount,avg(unit_price ) AVG_PRICE,
3 sum(qty*unit_price) SUM_TOT,
4 RATIO_TO_REPORT(sum(qty*unit_price )) over(partition by stock_id)*2 PCT
5 from t_dist
6 group by stock_id,rollup((type_cd,discount));
STOCK_ID TYPE_CD DISCOUNT AVG_PRICE SUM_TOT PCT
------------------------------------------