create table t( bom_code varchar2(20), lrsl number(19,2), price number(19,4), je number(19,4), fsrq date, gys_code varchar2(20) ); insert into t values('物料1',200,5,1000,to_date('2012.01.01','yyyy.mm.dd'),'供应商1'); insert into t values('物料1',100,10,1000,to_date('2012.01.02','yyyy.mm.dd'),'供应商2'); insert into t values('物料1',2000,4,8000,to_date('2012.01.03','yyyy.mm.dd'),'供应商3'); insert into t values('物料1',100,3,300,to_date('2012.02.01','yyyy.mm.dd'),'供应商1'); insert into t values('物料1',300,7,2100,to_date('2012.02.02','yyyy.mm.dd'),'供应商2'); insert into t values('物料1',200,5,1000,to_date('2012.02.03','yyyy.mm.dd'),'供应商3'); insert into t values('物料2',200,5,1000,to_date('2012.04.01','yyyy.mm.dd'),'供应商1'); insert into t values('物料2',100,10,1000,to_date('2012.04.02','yyyy.mm.dd'),'供应商2'); insert into t values('物料2',2000,4,8000,to_date('2012.04.03','yyyy.mm.dd'),'供应商3'); insert into t values('物料2',100,3,300,to_date('2012.02.01','yyyy.mm.dd'),'供应商1'); insert into t values('物料2',300,7,2100,to_date('2012.02.02','yyyy.mm.dd'),'供应商2'); insert into t values('物料2',200,5,1000,to_date('2012.02.03','yyyy.mm.dd'),'供应商3');select t.bom_code 物料代码, t.gys_code 供应商名称, min(t.fsrq) 起始日期, sum(t.lrsl) 订货数量, sum(t.lrsl*t.price) 订货金额,--如果你的je正确,也可以sum(t.je) max(t.price) 最高价格, avg(t.price) 平均价格, min(t.price) 最低价格, max(t2.price) 最新价格 from t, (select t1.bom_code,t1.gys_code,price from t, (select bom_code,gys_code,max(fsrq) fsrq from t group by bom_code,gys_code) t1 where t.bom_code=t1.bom_code and t.gys_code=t1.gys_code and t.fsrq=t1.fsrq) t2 where t.bom_code=t2.bom_code and t.gys_code=t2.gys_code group by t.bom_code,t.gys_code order by t.bom_code,t.gys_code; / --结果 物料代码 供应商名称 起始日期 订货数量 订货金额 最高价格 平均价格 最低价格 ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- 最新价格 ---------- 物料1 供应商1 01-1月 -12 300 1300 5 4 3 3
物料1 供应商2 02-1月 -12 400 3100 10 8.5 7 7
物料1 供应商3 03-1月 -12 2200 9000 5 4.5 4 5
物料2 供应商1 01-2月 -12 300 1300 5 4 3 5
物料2 供应商2 02-2月 -12 400 3100 10 8.5 7 10
物料2 供应商3 03-2月 -12 2200 9000 5 4.5 4 4 已选择6行。
zhangandli,我这个表有100多万行的数据,以上的SQL要执行很长时间
--最新价格 可以采用分析函数来算,效率要高一点:(示例数据如#2) select bom_code 物料代码 ,gys_code 供应商名称 ,min(fsrq) 起始日期 ,sum(lrsl) 订货数量 ,sum(je) 订货金额 ,max(price) 最高价格 ,avg(price) 平均价格 ,min(price) 最低价格 ,min(price) keep (dense_rank first order by fsrq desc) 最新价格 from t group by bom_code,gys_code; 另外,如果是几百万行的话,最好是新建一个冗余表(表格内容就是本报表),在每次发生采购业务时,更新这个冗余表,这样的话,以后要查这些数据时,直接查这个冗余表就行了。
create table t(
bom_code varchar2(20),
lrsl number(19,2),
price number(19,4),
je number(19,4),
fsrq date,
gys_code varchar2(20)
);
insert into t values('物料1',200,5,1000,to_date('2012.01.01','yyyy.mm.dd'),'供应商1');
insert into t values('物料1',100,10,1000,to_date('2012.01.02','yyyy.mm.dd'),'供应商2');
insert into t values('物料1',2000,4,8000,to_date('2012.01.03','yyyy.mm.dd'),'供应商3');
insert into t values('物料1',100,3,300,to_date('2012.02.01','yyyy.mm.dd'),'供应商1');
insert into t values('物料1',300,7,2100,to_date('2012.02.02','yyyy.mm.dd'),'供应商2');
insert into t values('物料1',200,5,1000,to_date('2012.02.03','yyyy.mm.dd'),'供应商3');
insert into t values('物料2',200,5,1000,to_date('2012.04.01','yyyy.mm.dd'),'供应商1');
insert into t values('物料2',100,10,1000,to_date('2012.04.02','yyyy.mm.dd'),'供应商2');
insert into t values('物料2',2000,4,8000,to_date('2012.04.03','yyyy.mm.dd'),'供应商3');
insert into t values('物料2',100,3,300,to_date('2012.02.01','yyyy.mm.dd'),'供应商1');
insert into t values('物料2',300,7,2100,to_date('2012.02.02','yyyy.mm.dd'),'供应商2');
insert into t values('物料2',200,5,1000,to_date('2012.02.03','yyyy.mm.dd'),'供应商3');select t.bom_code 物料代码,
t.gys_code 供应商名称,
min(t.fsrq) 起始日期,
sum(t.lrsl) 订货数量,
sum(t.lrsl*t.price) 订货金额,--如果你的je正确,也可以sum(t.je)
max(t.price) 最高价格,
avg(t.price) 平均价格,
min(t.price) 最低价格,
max(t2.price) 最新价格
from t,
(select t1.bom_code,t1.gys_code,price from t,
(select bom_code,gys_code,max(fsrq) fsrq
from t group by bom_code,gys_code) t1
where t.bom_code=t1.bom_code
and t.gys_code=t1.gys_code and t.fsrq=t1.fsrq) t2
where t.bom_code=t2.bom_code and t.gys_code=t2.gys_code
group by t.bom_code,t.gys_code
order by t.bom_code,t.gys_code;
/
--结果
物料代码 供应商名称 起始日期 订货数量 订货金额 最高价格 平均价格 最低价格
---------- ---------- -------------- ---------- ---------- ---------- ---------- ----------
最新价格
----------
物料1 供应商1 01-1月 -12 300 1300 5 4 3
3
物料1 供应商2 02-1月 -12 400 3100 10 8.5 7
7
物料1 供应商3 03-1月 -12 2200 9000 5 4.5 4
5
物料2 供应商1 01-2月 -12 300 1300 5 4 3
5
物料2 供应商2 02-2月 -12 400 3100 10 8.5 7
10
物料2 供应商3 03-2月 -12 2200 9000 5 4.5 4
4
已选择6行。
--最新价格 可以采用分析函数来算,效率要高一点:(示例数据如#2)
select bom_code 物料代码
,gys_code 供应商名称
,min(fsrq) 起始日期
,sum(lrsl) 订货数量
,sum(je) 订货金额
,max(price) 最高价格
,avg(price) 平均价格
,min(price) 最低价格
,min(price) keep (dense_rank first order by fsrq desc) 最新价格
from t
group by bom_code,gys_code;
另外,如果是几百万行的话,最好是新建一个冗余表(表格内容就是本报表),在每次发生采购业务时,更新这个冗余表,这样的话,以后要查这些数据时,直接查这个冗余表就行了。