日期:2014-05-18  浏览次数:20704 次

这种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.商品)
--or
select * 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 #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
*/

------解决方案--------------------
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 #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
*/