col1 col2
20071201 0.0
20071201 0.0
20071202 0.0
20071202 0.0
20071204 0.0
20071204 0.0
20071205 0.0
20071205 0.0
20071208 0.0
20071208 0.0
20071208 0.0
要取得离某一天最近的1天的所有记录,比如20071202,如何写?
20071201 0.0
20071201 0.0
20071202 0.0
20071202 0.0
20071204 0.0
20071204 0.0
20071205 0.0
20071205 0.0
20071208 0.0
20071208 0.0
20071208 0.0
要取得离某一天最近的1天的所有记录,比如20071202,如何写?
set @t = '2007-12-03'
select *
from tablename
where col1 = convert(varchar,dateadd(d,-1,@t),120)
---
select *
from tablename
where col1 = (select top 1 col1 from tablename where col1 > '20071202' order by col1 )
20071201 0.0
20071201 0.0
20071202 0.0
20071202 0.0
20071204 0.0
20071204 0.0
20071205 0.0
20071205 0.0
20071208 0.0
20071208 0.0
20071208 0.0
要取得离某一天最近的1天的所有记录,比如20071202,如何写?
declare @dt as varchar(8)
set @dt = '20071202'select t.* from tb t where col1 in (select col1 = max(col1) from tb where col1 < @dt)
insert into tb values('20071201', 0.0)
insert into tb values('20071201', 0.0)
insert into tb values('20071202', 0.0)
insert into tb values('20071202', 0.0)
insert into tb values('20071204', 0.0)
insert into tb values('20071204', 0.0)
insert into tb values('20071205', 0.0)
insert into tb values('20071205', 0.0)
insert into tb values('20071208', 0.0)
insert into tb values('20071208', 0.0)
insert into tb values('20071208', 0.0)
go
declare @dt as datetime
set @dt = '20071202'select A.* from tb A where abs(datediff(day , @dt , col1)) =
(select min(t) t from(select col1,col2,t = abs(datediff(day , @dt , col1)) from tb) m where t <> 0 )drop table tb/*
col1 col2
----------------------- ---------------------------------------
2007-12-01 00:00:00.000 0.0
2007-12-01 00:00:00.000 0.0(2 行受影响)
*/