[PL/SQL]如何实现比较复杂的分组、小计与合计
[PL/SQL]如何实现比较复杂的分组、小计与合计
--测试代码
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%
