id data time
001 <二进制> 2008-10-1 09:00:00
001 <二进制> 2008-10-2 07:00:00
001 <二进制> 2008-10-3 08:00:00
001 <二进制> 2008-10-4 18:00:00我现在只知道searchtime = 2008-10-2 08:00:00我现在通过这个条件要把001 <二进制> 2008-10-2 07:00:00和001 <二进制> 2008-10-3 08:00:00这两行显示出来.大侠们,救命啊,我搞了好久没搞出来.
001 <二进制> 2008-10-1 09:00:00
001 <二进制> 2008-10-2 07:00:00
001 <二进制> 2008-10-3 08:00:00
001 <二进制> 2008-10-4 18:00:00我现在只知道searchtime = 2008-10-2 08:00:00我现在通过这个条件要把001 <二进制> 2008-10-2 07:00:00和001 <二进制> 2008-10-3 08:00:00这两行显示出来.大侠们,救命啊,我搞了好久没搞出来.
select * from tb
where convert(char(10),time,120)=convert(char(10),cast('2008-10-2 08:00:00' as datetime ),120)
or time= dateadd(day,1,cast('2008-10-2 08:00:00' as datetime ))结果:001 <二进制> 2008-10-02 07:00:00.000
001 <二进制> 2008-10-03 08:00:00.000
drop table tb
Go
Create table tb([id] nvarchar(3),[data] nvarchar(5),[time] datetime)
Insert tb
select N'001',N'<二进制>',N'2008-10-1 09:00:00' union all
select N'001',N'<二进制>',N'2008-10-2 07:00:00' union all
select N'001',N'<二进制>',N'2008-10-3 08:00:00' union all
select N'001',N'<二进制>',N'2008-10-4 18:00:00'
Go
declare @searchtime datetime
set @searchtime='2008-10-2 08:00:00'
Select * from tb
where time = (select top 1 time from tb where time<=@searchtime order by datediff(s,@searchtime,time) desc )
or time = (select top 1 time from tb where time>=@searchtime order by datediff(s,@searchtime,time) )
/*
id data time
---- ----- -----------------------
001 <二进制> 2008-10-02 07:00:00.000
001 <二进制> 2008-10-03 08:00:00.000
*/
declare @T table ([id] varchar(3),[data] varchar(8),[time] datetime)
insert into @T
select '001','<二进制>','2008-10-1 09:00:00' union all
select '001','<二进制>','2008-10-2 07:00:00' union all
select '001','<二进制>','2008-10-3 08:00:00' union all
select '001','<二进制>','2008-10-4 18:00:00'select * from @T
--Code
declare @searchtime Datetime
set @searchtime = '2008-10-2 08:00:00'
;with cte as
(select px=ROW_NUMBER() over(order by [time]),* from @T)select id,data,time from cte where px in
(
(select MAX(px) from cte where [time]<=@searchtime),
(select min(px) from cte where [time]>=@searchtime)
)--Result
/*
id data time
---- -------- -----------------------
001 <二进制> 2008-10-02 07:00:00.000
001 <二进制> 2008-10-03 08:00:00.000
*/
from tb a
where datediff(day,time,'2008-10-2 8:00:00') in (select top 2 min(datediff(day,'2008-10-2 8:00:00',time)) from tb group by day(time))
结果:
001 <二进制> 2008-10-02 07:00:00.000
001 <二进制> 2008-10-03 08:00:00.000
drop table tb
Go
Create table tb([id] nvarchar(3),[data] nvarchar(5),[time] datetime)
Insert tb
select N'001',N'<二进制>',N'2008-10-1 09:00:00' union all
select N'001',N'<二进制>',N'2008-10-2 07:00:00' union all
select N'001',N'<二进制>',N'2008-10-3 08:00:00' union all
select N'001',N'<二进制>',N'2008-10-4 18:00:00'
Go
declare @searchtime datetime
set @searchtime='2008-10-2 08:00:00'
select * FROM TB where time = (select MAX(time) from tb where time<=@searchtime )
UNION ALL
select * FROM TB WHERE TIME = (select MIN(time) from tb where time>=@searchtime )
/*
(所影响的行数为 4 行)id data time
---- ----- ------------------------------------------------------
001 <二进制> 2008-10-02 07:00:00.000
001 <二进制> 2008-10-03 08:00:00.000(所影响的行数为 2 行)
*/
INSERT @t SELECT '001' ,0x131,'2008-10-1 09:00:00'
UNION ALL SELECT '001' ,0x11,'2008-10-2 07:00:00'
UNION ALL SELECT '001' , 0x11f,'2008-10-3 08:00:00'
UNION ALL SELECT '001', 0x112,'2008-10-4 18:00:00'DECLARE @searchTime DATETIME
SET @searchtime = '2008-10-2 08:00:00'
SELECT * FROM @t a
WHERE 1>
(
SELECT COUNT(*) FROM @t
WHERE CASE WHEN
a.time<@searchtime THEN 1
ELSE -1
END
*
DATEDIFF(ms,a.time,time)>0
AND CASE WHEN
a.time<@searchtime THEN 1
ELSE -1
END
*
DATEDIFF(ms,time,@searchtime)>0
)/*
001 0x11000000000000000000000000000000 2008-10-02 07:00:00.000
001 0x011F0000000000000000000000000000 2008-10-03 08:00:00.000
*/
drop table tb
Go
Create table tb([id] nvarchar(3),[data] BINARY(20),[time] datetime)
Insert tb
select N'001',cast('<二进制>'as binary),N'2008-10-1 09:00:00' union all
select N'001',cast('<二进制>'as binary),N'2008-10-2 07:00:00' union all
select N'001',cast('<二进制>'as binary),N'2008-10-3 08:00:00' union all
select N'001',cast('<二进制>'as binary),N'2008-10-4 18:00:00'
Go
declare @searchtime datetime
set @searchtime='2008-10-2 08:00:00'
select [id],[data],[time] FROM TB where time = (select MAX(time) from tb where time<=@searchtime )
UNION ALL
select [id],[data],[time] FROM TB WHERE TIME = (select MIN(time) from tb where time>=@searchtime )
/*id data time
---- ------------------------------------------ ------------------------------------------------------
001 0x3CB6FEBDF8D6C63E000000000000000000000000 2008-10-02 07:00:00.000
001 0x3CB6FEBDF8D6C63E000000000000000000000000 2008-10-03 08:00:00.000(所影响的行数为 2 行)*/
select id,data,time
from (select top 2 tb.* ,abs(datediff(hh,'2008-10-2 8:00:00',time)) as [datediff] from tb order by abs(datediff(hh,'2008-10-2 8:00:00',time))) b结果:
001 <二进制> 2008-10-02 07:00:00.000
001 <二进制> 2008-10-02 10:00:00.000
加了一些数据:if not object_id('tb') is null
drop table tb
Go
Create table tb([id] nvarchar(3),[data] nvarchar(5),[time] datetime)
Insert tb
select N'001',N'<二进制>',N'2008-10-1 09:00:00' union all
select N'001',N'<二进制>',N'2008-10-2 07:00:00' union all
select N'001',N'<二进制>',N'2008-10-3 08:00:00' union all
select N'001',N'<二进制>',N'2008-10-4 18:00:00' union all
select N'001',N'<二进制>',N'2008-10-1 18:00:00' union all
select N'001',N'<二进制>',N'2008-10-2 18:00:00' union all
select N'001',N'<二进制>',N'2008-10-3 07:00:00' union all
select N'001',N'<二进制>',N'2008-10-2 10:00:00'
2008-1-1
2008-1-2
2008-1-3
2008-1-9
2008-1-10@searchtime = 2008-1-4这样的数据,按楼主要求应该是取到1-3
和
1-9abs会取1-3和1-2. 当同方向 多个值与参照量偏差 小于 反方向值与参照量的最小值时,就会取到同方向的多个值。
你的数据我加一条就可以了。
select N'001',N' <二进制>',N'2008-10-1 09:00:00' union all
select N'001',N' <二进制>',N'2008-10-2 07:00:00' union all
select N'001',N' <二进制>',N'2008-10-2 07:20:00' union all --这里加一条,呵呵,看看结果是多少
select N'001',N' <二进制>',N'2008-10-3 08:00:00' union all
select N'001',N' <二进制>',N'2008-10-4 18:00:00' union all
select N'001',N' <二进制>',N'2008-10-1 18:00:00' union all
select N'001',N' <二进制>',N'2008-10-2 18:00:00' union all
select N'001',N' <二进制>',N'2008-10-3 07:00:00' union all
select N'001',N' <二进制>',N'2008-10-2 10:00:00'
对于
2008-1-1
2008-1-1
2008-1-2
2008-1-3
2008-1-9
2008-1-10 @searchtime = 2008-1-4 你的理解是 结果应该是
1-3
和
1-9 我的理解是
2008-1-2
2008-1-3