select PartNo,Price,DateIn
from
(
select DateIn,Price,Row_number()over(partition by PartNo
order by DATEDIFF (dd,CAST ('2009-4-1' as datetime),CAST(DateIn as datetime))) cnt,PartNo
from table
)A
where cnt=1
from
(
select DateIn,Price,Row_number()over(partition by PartNo
order by DATEDIFF (dd,CAST ('2009-4-1' as datetime),CAST(DateIn as datetime))) cnt,PartNo
from table
)A
where cnt=1
if object_id('[tab]') is not null drop table [tab]
create table [tab]([PartNo] varchar(4),[Price] numeric(2,1),[DateIn] datetime)
insert [tab]
select '0001',1.0,'2009-4-23' union all
select '0001',1.2,'2009-7-3' union all
select '0002',2.0,'2009-5-1' union all
select '0002',2.1,'2009-6-1' union all
select '0002',2.2,'2009-7-1'
select PartNo,price,DateIn
from (select *,palce=abs(datediff(dd,'2009-4-1',DateIn)) from tab) t
where not exists (select 1 from tab where t.PartNo=PartNo and abs(datediff(dd,'2009-4-1',DateIn))<t.palce)/*
PartNo price DateIn
------ ----- ------------------------------------------------------
0001 1.0 2009-04-23 00:00:00.000
0002 2.0 2009-05-01 00:00:00.000(所影响的行数为 2 行)*/
drop table tab
我已经写了一个查询把DateIn和2009-4-1的差值求出来
但是后面是怎么样取到最小的那个就看不懂了