我有这些数据
Table: Shifts
---------------------------------
Shift_Start Shift_End
---------------------------------
6:00 16:30
16:45 22:15
22:15 6:00 (Next day)
----------------------------------
我只需要选择一条当前时间介于shift_start
和之间的记录shift_end
例如我试过
Select * from Shifts where current_time between shift_start and shift_end
或者
Select first 1, shift_start, shift_end from Shifts
order by shift_end - current_time
但是没有什么能正常工作,有什么想法吗?
也许这会做您想要的:
Select *
from Shifts
where shift_start < shift_end and current_time between shift_start and shift_end or
shift_start > shift_end and not current_time between shift_start and shift_end;
如果问题确实是如何只获得一场比赛:
Select first 1 *
from Shifts
where shift_start < shift_end and current_time between shift_start and shift_end or
shift_start > shift_end and not current_time between shift_start and shift_end;
编辑:
要先返回匹配的时间段,然后再返回其他时间段,可以使用order by
代替where
:
Select first 1 *
from Shifts
order by (case when shift_start < shift_end and current_time between shift_start and shift_end or
shift_start > shift_end and not current_time between shift_start and shift_end
then 0 else 1
end);
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句