表名：vendor_price
表中字段：vendor_id,part_id,effec_date,unit_price,currency_id,unit,price_term ......
vendor_id:供应商代码
part_id:物料代码
effec_date:采购价起效日期（关键）
unit_price:采购价格
currency_id:结算货币
unit:单位
price_term:结算方式
需求：按part_id查询effec_date时间最新的对应的unit_price信息,不用考虑供应商
vendor_id part_id effec_date unit_price currency_id unit price_term
A138 009.0003 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A138 009.0005 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0005 2011-06-23 00:00:00 0.000280 USD 01 NULL
A141 009.0006 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0006 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A138 009.0008 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0008 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0009 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A138 009.0011 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0011 2011-06-23 00:00:00 0.000280 USD 01 NULL
A141 009.0012 2011-06-23 00:00:00 0.000280 USD 01 NULL
A141 009.0013 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0013 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
B057 009.0013 2008-12-04 00:00:00 0.002400 RMB 01 货到付款
A138 009.0014 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
A141 009.0014 2011-07-26 00:00:00 0.000280 USD 01 NULL
A141 009.0015 2011-06-23 00:00:00 0.000280 USD 01 NULL
A138 009.0015 2010-04-10 00:00:00 0.002200 RMB 01 货到付款
------解决方案--------------------
- SQL code
;WITH tmp AS ( SELECT * , rn = row_number() OVER ( PARTITION BY part_id ORDER BY effec_date DESC ) FROM vendor_price ) SELECT * FROM tmp WHERE rn = 1