查找最新报价----谈group by的简单用法

今天要写一物料成本的报表。

　　背景：系统中一个物料可能对应多个供应商 itemvend(rank)，一个物料也可能对应多个报价itemvendprice

很自然想到　：

根据生效日期按降序排列，取top 1

select top 1f.item,f.vend_num,f.effect_date ,f.brk_cost##1

from itemVendPrice f,itemVend g where f.item=g.item and f.Vend_Num=g.Vend_Num and g.rank=1) b

order by f.effect_date desc

可是不能取得所有物料的报价

想到用max函数

select b.item,b.vend_num,max(b.effect_date) as effect_date, b.brk_cost##1from

(select f.item,f.vend_num,f.effect_date

from itemVendPrice f,itemVend g where f.item=g.item and f.Vend_Num=g.Vend_Num and g.rank=1) b

group by b.item,b.vend_num

语法错误－－单价成本不同

故先不去单价成本，最后关联之

select c.item,c.vend_num,c.effect_date,c.brk_cost##1 from itemVendPrice c,

(select b.item,b.vend_num,max(b.effect_date) as effect_date from

(select f.item,f.vend_num,f.effect_date

from itemVendPrice f,itemVend g where f.item=g.item and f.Vend_Num=g.Vend_Num and g.rank=1) b

group by b.item,b.vend_num) a

where c.item=a.item and c.vend_num=a.vend_num and c.effect_date=a.effect_date

测试结果ok -----