表A 日期 商品 零售价 批发价
2010-1-1 001 600 500
2010-1-1 002 700 550
2010-1-1 003 400 300
2010-2-1 001 650 550
2010-2-1 002 750 600
2010-5-1 004 650 550
2010-5-1 005 750 600 显示出来的结果为:
日期 商品 零售价 批发价
2010-5-1 004 650 550
2010-5-1 005 750 600
2010-2-1 001 650 550
2010-2-1 002 750 600
2010-1-1 003 400 300
意思是显示最新价格
2010-1-1 001 600 500
2010-1-1 002 700 550
2010-1-1 003 400 300
2010-2-1 001 650 550
2010-2-1 002 750 600
2010-5-1 004 650 550
2010-5-1 005 750 600 显示出来的结果为:
日期 商品 零售价 批发价
2010-5-1 004 650 550
2010-5-1 005 750 600
2010-2-1 001 650 550
2010-2-1 002 750 600
2010-1-1 003 400 300
意思是显示最新价格
where 日期=(select max(日期) from tb where 商品=a.商品)
--or
select * from tb a
where not eixsts(select 1 from tb where 商品=a.商品 and 日期>a.日期)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[商品] nvarchar(3),[零售价] int,[批发价] int)
Insert #T
select '2010-1-1',N'001',600,500 union all
select '2010-1-1',N'002',700,550 union all
select '2010-1-1',N'003',400,300 union all
select '2010-2-1',N'001',650,550 union all
select '2010-2-1',N'002',750,600 union all
select '2010-5-1',N'004',650,550 union all
select '2010-5-1',N'005',750,600
Go
Select * from #T as a where not exists(select 1 from #T where [商品]=a.[商品] and [日期]>a.[日期]) order by 1 desc,2
/*
日期 商品 零售价 批发价
2010-05-01 00:00:00.000 004 650 550
2010-05-01 00:00:00.000 005 750 600
2010-02-01 00:00:00.000 001 650 550
2010-02-01 00:00:00.000 002 750 600
2010-01-01 00:00:00.000 003 400 300
*/
where not exists(select 1 from tb where 商品=a.商品 and 日期>a.日期)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[商品] nvarchar(3),[零售价] int,[批发价] int)
Insert #T
select '2010-1-1',N'001',600,500 union all
select '2010-1-1',N'002',700,550 union all
select '2010-1-1',N'003',400,300 union all
select '2010-2-1',N'001',650,550 union all
select '2010-2-1',N'002',750,600 union all
select '2010-5-1',N'004',650,550 union all
select '2010-5-1',N'005',750,600
Go
select [日期],[商品],[零售价],[批发价]
from (Select * ,row=row_number()over(partition by [商品] order by [日期] desc) from #T) as a
where row=1
order by 1 desc,2
/*
日期 商品 零售价 批发价
2010-05-01 00:00:00.000 004 650 550
2010-05-01 00:00:00.000 005 750 600
2010-02-01 00:00:00.000 001 650 550
2010-02-01 00:00:00.000 002 750 600
2010-01-01 00:00:00.000 003 400 300
*/