使用SQL查询物料最新采购价格的示例

--创建临时表，插入测试数据
if Object_id(‘Tempdb..#temp1‘) is not null drop table #temp1
create table #temp1(ItemNumber varchar(10),PurchDate date,PurchPrice decimal(10,2))
insert into #temp1(ItemNumber,PurchDate,PurchPrice)
select ‘Item01‘, ‘2016-1-8‘,3.33 union all
select ‘Item01‘, ‘2016-5-8‘,2.22 union all
select ‘Item01‘, ‘2016-3-8‘,1.11 union all
select ‘Item02‘, ‘2016-3-9‘,4.44 union all
select ‘Item02‘, ‘2016-5-9‘,5.55 union all
select ‘Item02‘, ‘2016-1-9‘,6.66 union all
select ‘Item03‘, ‘2016-1-7‘,9.99 union all
select ‘Item03‘, ‘2016-3-7‘,8.88 union all
select ‘Item03‘, ‘2016-3-7‘,7.77
 
--查询所有信息
select * from #temp1 order by ItemNumber asc,PurchDate desc,PurchPrice asc
 
--使用row_number() over partition by 查询物料的最新采购价格，如果同一日期有多个价格取最小值
select *
from (
    select ItemNumber,PurchDate,PurchPrice,
    row_number() over(partition by ItemNumber order by ItemNumber asc,PurchDate desc,PurchPrice asc) row_num
    from #temp1) t1
where t1.row_num=1