一个查询语句，需要您的帮忙
a表三列：id为int类型，dt列为datetime类型，data列为int   类型
id             dt                               data
1       2007-05-1   9:00:10           100
1       2007-05-1   9:04:10           101
1       2007-05-1   9:08:10           102
1       2007-05-2   9:01:10           103
1       2007-05-2   9:05:18           104
查询是这样的：
假设我要id   为1的数据，时间是5月1号和2号，离9点最近的数据
id             dt                             today9               yesterday9
1     2007-05-1   9:00:10           100                       103


帮着出出主意吧

------解决方案--------------------
declare @a table(id int, dt datetime,data int)
insert @a select 1 , '2007-05-1 9:00:10 ', 100
union all select 1 , '2007-05-1 9:04:10 ', 101
union all select 1 , '2007-05-1 9:08:10 ', 102
union all select 1 , '2007-05-2 9:01:10 ', 103
union all select 1 , '2007-05-2 9:05:18 ', 104


select id,dt,convert(varchar(10),dt,120) d,abs(datediff(ss,cast(convert(varchar(10),dt,120)+ ' 09:00:00 ' as datetime),dt) ) ti, data into #tmp from @a

select * from #tmp a where not exists(select 1 from #tmp where id=a.id and d=a.d and ti <a.ti)

drop table #tmp
------解决方案--------------------
try


Select
TOP 1
id,
dt,
data As today9,
(Select TOP 1 data From A Where id = T.id And DateDiff(dd, T.dt, dt) = 1 And Datepart(hh, dt) > =9 Order By dt) As tomorrow9
From A T
Where Datepart(hh, dt) > =9 And DateDiff(dd, dt, ' 2007-05-01 ') = 0
Order By dt

------解决方案--------------------
Create Table A
(id Int,
dt Datetime,
data Int)
Insert A Select 1, '2007-05-1 9:00:10 ', 100
Union All Select 1, '2007-05-1 9:04:10 ', 101
Union All Select 1, '2007-05-1 9:08:10 ', 102
Union All Select 1, '2007-05-2 9:01:10 ', 103
Union All Select 1, '2007-05-2 9:05:18 ', 104
GO
Select
TOP 1
id,
dt,
data As today9,
(Select TOP 1 data From A Where id = T.id And DateDiff(dd, T.dt, dt) = 1 And Datepart(hh, dt) > =9 Order By dt) As tomorrow9
From A T
Where Datepart(hh, dt) > =9 And DateDiff(dd, dt, ' 2007-05-01 ') = 0
Order By dt
GO
Drop Table A
--Result
/*
id dt today9 tomorrow9
1 2007-05-01 09:00:10.000 100 103
*/