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

SQL中 查询与指定时间最相近的记录
表中列为
ID 主键
Time datetime
Location nvarchar(50)
Name nvarchar(50)
数据类型如下
ID Time Location Name
1 2012/02/01 22:12:00 合肥 合肥
2 2012/02/01 22:12:03 合肥 合肥
3 2012/02/01 22:13:00 合肥 合肥
4 2012/02/02 22:15:06 合肥 合肥
5 2012/02/02 22:15:50 合肥 合肥
比如取与2012/02/02 22:15:00 最相近的记录 
应该返回
4 2012/02/02 22:15:06 合肥 合肥
记录 SQL 语句怎么写?


------解决方案--------------------
SQL code

create table tb (id int, time datetime,location varchar(50),name varchar(50))
insert into tb select 1,'2012-2-1 22:12:00','合肥','合肥'
insert into tb select 2,'2012-2-1 22:12:03','合肥','合肥'
insert into tb select 3,'2012-2-1 22:13:00','合肥','合肥'
insert into tb select 4,'2012-2-2 22:15:06','合肥','合肥'
insert into tb select 5,'2012-2-2 22:15:50','合肥','合肥'

with aa as(
select time,case when datediff(ss,'2012/02/02 22:15:00' ,time)<0 then -datediff(ss,'2012/02/02 22:15:00',time) else datediff(ss,'2012/02/02 22:15:00',time) end aa from tb 
where time!='2012/02/02 22:15:00') 
select * from tb where time in (select time from aa a where not exists(select 1 from aa b where a.aa>b.aa))

------解决方案--------------------
SQL code
if Object_id('tb1') is not null Drop table tb1;
go
create table tb1([ID] int primary key, [Time] datetime, [Location] nvarchar(50), [Name] nvarchar(50));
go
insert into tb1
select 1, '2012/02/01 22:12:00', '合肥', '合肥' union all
select 2, '2012/02/01 22:12:03', '合肥', '合肥' union all
select 3, '2012/02/01 22:13:00', '合肥', '合肥' union all
select 4, '2012/02/02 22:15:06', '合肥', '合肥' union all
select 5, '2012/02/02 22:15:50', '合肥', '合肥';

select * from tb1;

declare @t datetime;
Set @t = '2012/02/02 22:15:00';

select top 1* 
from tb1 
order by abs(datediff(ms, @t, [Time])) 


/*
ID          Time                    Location        Name
----------- ----------------------- --------------- -----------------
4           2012-02-02 22:15:06.000 合肥            合肥
*/