这种SQL语句这么写？要执行高效！该怎么解决

这种SQL语句这么写？要执行高效！
表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 


意思是显示最新价格


[解决办法]

SQL code

select * from tb a where 日期=(select max(日期) from tb where 商品=a.商品)--orselect * from tb a where not eixsts(select 1 from tb where 商品=a.商品 and 日期>a.日期)
[解决办法]
SQL code
--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([日期] Datetime,[商品] nvarchar(3),[零售价] int,[批发价] int)Insert #Tselect '2010-1-1',N'001',600,500 union allselect '2010-1-1',N'002',700,550 union allselect '2010-1-1',N'003',400,300 union allselect '2010-2-1',N'001',650,550 union allselect '2010-2-1',N'002',750,600 union allselect '2010-5-1',N'004',650,550 union allselect '2010-5-1',N'005',750,600GoSelect * 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    5502010-05-01 00:00:00.000    005    750    6002010-02-01 00:00:00.000    001    650    5502010-02-01 00:00:00.000    002    750    6002010-01-01 00:00:00.000    003    400    300*/
[解决办法]
SQL code
select * from tb a where not exists(select 1 from tb where 商品=a.商品 and 日期>a.日期)
[解决办法]
SQL code
或--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([日期] Datetime,[商品] nvarchar(3),[零售价] int,[批发价] int)Insert #Tselect '2010-1-1',N'001',600,500 union allselect '2010-1-1',N'002',700,550 union allselect '2010-1-1',N'003',400,300 union allselect '2010-2-1',N'001',650,550 union allselect '2010-2-1',N'002',750,600 union allselect '2010-5-1',N'004',650,550 union allselect '2010-5-1',N'005',750,600Goselect [日期],[商品],[零售价],[批发价]from (Select * ,row=row_number()over(partition by [商品] order by [日期] desc) from #T) as a where row=1order by 1 desc,2/*日期    商品    零售价    批发价2010-05-01 00:00:00.000    004    650    5502010-05-01 00:00:00.000    005    750    6002010-02-01 00:00:00.000    001    650    5502010-02-01 00:00:00.000    002    750    6002010-01-01 00:00:00.000    003    400    300*/