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我想应该是这个意思
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
感觉好像是酱紫的 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
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))
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))
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我想应该是这个意思
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
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
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))
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))