sql查询最近时间段

[xianyouyu88]

表A
字段ID 字段TIME
1 09:00:00
2 12:00:00
3 15:00:00

查询出09:30 的字段ID是1
查询出12:01 的字段ID是2
查询出20:56 的字段ID是3
查询出08:59 的字段ID是3
时间段是循环的.

[山寨DBA]

没看出来有什么规律可循，多给一些数据看看呐，这看不出规律呀

[xianyouyu88]

比如09:30,那它往前最近的时间就是09:00

[还在加载中灬]

DECLARE @TIME DATETIME
SET @TIME='08:59'
SELECT 字段ID FROM(
	SELECT
		ISNULL(T1.字段ID,MAX(T2.字段ID)OVER())字段ID
		,ISNULL(T1.字段TIME,'00:00:00')StartTime
		,ISNULL(T2.字段TIME,'23:59:59.997')EndTime
	FROM A T1
		FULL JOIN A T2 ON T1.字段ID+1=T2.字段ID
)T
WHERE @TIME>=StartTime
	AND @TIME<=EndTime

我想应该是这个意思

[jayzhihui]

     if OBJECT_ID('tempdb..#t')>0 drop table #t
     select cast('09:00:00' as time) ttime into #t 
     union all
     select cast('12:00:00' as time) ttime 
     union all
     select cast('15:00:00' as time) ttime 
 
 declare @time time = '8:59:00'
 if exists(select 1 from #t where @time > ttime)
 begin
 select top 1 ttime from #t where @time > ttime order by ttime desc 
 end
 else 
 begin
 select top 1 ttime from #t  order by ttime desc 
 end

[jayzhihui]

是不是这样理解~

[rings2000]

你表达得不太清楚吧

[无涯大者]

引用 4 楼 jayzhihui 的回复:

     if OBJECT_ID('tempdb..#t')>0 drop table #t
     select cast('09:00:00' as time) ttime into #t 
     union all
     select cast('12:00:00' as time) ttime 
     union all
     select cast('15:00:00' as time) ttime 
 
 declare @time time = '8:59:00'
 if exists(select 1 from #t where @time > ttime)
 begin
 select top 1 ttime from #t where @time > ttime order by ttime desc 
 end
 else 
 begin
 select top 1 ttime from #t  order by ttime desc 
 end

时间段内取开始时间的ID，这个应该已经实现了！

[jph1122]

感觉好像是酱紫的 select case when time>='09:00:00' and time<'12:00:00' then 1 when time>='12:00:00' and time<'15:00:00' then 2 when time>='15:00:00' or time<'09:00:00' then 3 else 0 end from #a

[zbdzjx]

declare @TestTime varchar(10)
set @TestTime='15:01:00';

with
cte1 as
(
    select '09:00:00' BeginTime union all
    select '12:00:00' BeginTime union all
    select '15:00:00' BeginTime 
)
select ISNULL((select max(BeginTime) from cte1 where BeginTime<@TestTime),(select MAX(BeginTime) from cte1))

[Just_Go_Yo]

select * from t1
select isnull((select top 1 id  from t1 where time<'09:30' order by time desc),(select top 1 id from t1 order by time desc))
select isnull((select top 1 id  from t1 where time<'12:01' order by time desc),(select top 1 id from t1 order by time desc))
select isnull((select top 1 id  from t1 where time<'20:56' order by time desc),(select top 1 id from t1 order by time desc))
select isnull((select top 1 id  from t1 where time<'08:59' order by time desc),(select top 1 id from t1 order by time desc))