使用SQL查询物料最新采购价格的示例
时间：2021-07-01 10:21:17
帮助过：3人阅读
创建临时表，插入测试数据
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
使用SQL查询物料最新采购价格的示例
标签：存在 null desc class 记录 create obj nbsp query
