oracle 实现小计,[PL/SQL]如何实现比较复杂的分组、小计与合计

[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%

http://www.htsjk.com/oracle/21405.html

www.htsjk.Com

true

http://www.htsjk.com/oracle/21405.html

NewsArticle

[PL/SQL]如何实现比较复杂的分组、小计与合计 [PL/SQL]如何实现比较复杂的分组、小计与合计 --测试代码create table t_dist( TYPE_CD NUMBER, BUYER_ID VARCHAR2(50), ORDER_DT DATE, SO_ID VARCHAR2(50) not null, ST...

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.

同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

相关文章

暂无相关文章