sql 求与当前时间最近的一条信息

[lei19890721]

假如有三条信息 1 a 2008-1-10

2 b 2008-8-10

3 c 2009-10-5
我现在要获得与今天（2009-11-23）最近的一条信息
也就是说要获得 第三条信息
通过时间来查询 sql怎么写啊

[子陌红尘]

select top 1 * from 表 order by abs(datediff(ss,getdate(),日期字段))

[sgtzzc]

select *
from tb t
where not exists(select 1 from tb where abs(datediff(dd,时间字段,getdate()))<abs(datediff(dd,t.时间字段,getdate())))

[icelovey]

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/23
-- Version:     SQL SERVER 2005
-- =============================================
declare @tb1 table([ID] int,[COL] varchar(1),[RDATE] datetime)
insert @tb1
select 1,'a','2008-1-10' union all
select 2,'b','2008-8-10' union all
select 3,'c','2009-10-5'

select TOP 1 * from @tb1 ORDER BY DATEDIFF(SS,GETDATE(),[RDATE]) DESC


--测试结果:
/*
ID          COL  RDATE
----------- ---- -----------------------
3           c    2009-10-05 00:00:00.000
*/

[icelovey]

老大出没

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-23 16:14:58
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[name] varchar(1),[time] datetime)
insert [tb]
select 1,'a','2008-1-10' union all
select 2,'b','2008-8-10' union all
select 3,'c','2009-10-5'
--------------开始查询--------------------------
select top 1 * from tb where time=(select max(time) from tb where datediff(dd,[time],getdate())>0)
----------------结果----------------------------
/* id          name time
----------- ---- -----------------------
3           c    2009-10-05 00:00:00.000

(1 行受影响)

*/

[ilmarezhou]

select top 1 * from order by abs(datediff(ss,getdate(),日期字段))

[feixianxxx]

--1楼砖砖的方法 有个小漏洞 就是也许 那个获得与今天（2009-11-23）最近的一条信息 可能
--不止一条 如果说表里有2条一样的 可以这样 
select top 1 with ties * from 表 order by abs(datediff(ss,getdate(),日期字段))

[lei19890721]

哦 对不起 我们提描述错了
刚刚试了下 都是对的
问题应该是这样的 id name date M_ID
1 a 2008-1-10 1

2 b 2008-8-10 1

3 c 2009-10-5 1

4 aa 2008-10-11 2
5 bb 2009-11-11 2
求 3 c 2009-10-5 1
5 bb 2009-11-11 2
离现在最近的

[feixianxxx]

;with cte as
(
   select top 1 * from 表 order by abs(datediff(ss,getdate(),date))
  union all
  select top 1 a.* from  表 a join cte b on a.m_id=b.M_id+1 
   order by a.id desc, abs(datediff(ss,getdate(),a.date))
)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-23 16:29:47
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[name] varchar(2),[date] datetime,[M_ID] int)
insert [tb]
select 1,'a','2008-1-10',1 union all
select 2,'b','2008-8-10',1 union all
select 3,'c','2009-10-5',1 union all
select 4,'aa','2008-10-11',2 union all
select 5,'bb','2009-11-11',2
--------------开始查询--------------------------
select
  * 
from
 tb t 
where 
 [date]=(select max([date]) from tb  where [M_ID]=t.[M_ID] and datediff(dd,[date],getdate())>0)
order by 
  1 
----------------结果----------------------------
/* id          name date                    M_ID
----------- ---- ----------------------- -----------
3           c    2009-10-05 00:00:00.000 1
5           bb   2009-11-11 00:00:00.000 2

(2 行受影响)
*/

[icelovey]

[Quote=引用 8 楼 lei19890721 的回复:]
哦  对不起  我们提描述错了 
刚刚试了下 都是对的
问题应该是这样的 id  name  date      M_ID
                  1    a    2008-1-10    1

                2    b    2008-8-10    1
             
                3    c    2009-10-5    1
               
                4    aa  2008-10-11    2
                5    bb  2009-11-11    2
  求  3    c    2009-10-5    1   
      5    bb  2009-11-11    2
  离现在最近的

               
[/Quote]

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/23
-- Version:     SQL SERVER 2005
-- =============================================
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([id] int,[name] varchar(2),[date] datetime,[M_ID] int)
insert [TB]
select 1,'a','2008-1-10',1 union all
select 2,'b','2008-8-10',1 union all
select 3,'c','2009-10-5',1 union all
select 4,'aa','2008-10-11',2 union all
select 5,'bb','2009-11-11',2

select * 
from [TB] t
where not exists(select 1 from [TB] where t.m_id=m_id and t.date<date)

drop table tb


--测试结果:
/*
id          name date                    M_ID
----------- ---- ----------------------- -----------
3           c    2009-10-05 00:00:00.000 1
5           bb   2009-11-11 00:00:00.000 2
*/

[lei19890721]

在不结贴答案太多了