我的MySQL查询在22:29:50可以，但是在22:30:00之后，并且end_time是第二天07:29:59查询不起作用。
我的桌子上班了
shift_name |开始时间| end_time shift1 | 07:30:00 | 15：30：00 shift2 | 15:30:00 | 22:30:00 shift3 | 22:30:00 | 07:30:00
我写以下查询
select shift_name from shift
where time('22:30:00') BETWEEN start_time and end_time;
CREATE TABLE shift (`shift_name` varchar(6), `start_time` time, `end_time` time);
INSERT INTO shift (`shift_name`, `start_time`, `end_time`) VALUES ('shift1', '07:30:00', '15:30:00'), ('shift2', '15:30:00', '22:30:00'), ('shift3', '22:30:00', '07:30:00');
select * from ( select cast('05:22' as time) t ) d cross join shift where (t >= start_time and t < end_time) or (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) or (t >= start_time and start_time > end_time )t | shift_name | start_time | end_time ：------- | ：--------- | ：--------- | ：------- 05:22:00 | shift3 | 22:30:00 | 07:30:00
select * from ( select cast('23:52' as time) t ) d cross join shift where (t >= start_time and t < end_time) or (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) or (t >= start_time and start_time > end_time )t | shift_name | start_time | end_time ：------- | ：--------- | ：--------- | ：------- 23:52:00 | shift3 | 22:30:00 | 07:30:00
select * from ( select cast('10:52' as time) t ) d cross join shift where (t >= start_time and t < end_time) or (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) or (t >= start_time and start_time > end_time )t | shift_name | start_time | end_time ：------- | ：--------- | ：--------- | ：------- 10:52:00 | shift1 | 07:30:00 | 15:30:00
select * from ( select cast('19:52' as time) t ) d cross join shift where (t >= start_time and t < end_time) or (t >= cast('00:00' as time) and t < end_time and start_time > end_time ) or (t >= start_time and start_time > end_time )t | shift_name | start_time | end_time ：------- | ：--------- | ：--------- | ：------- 19:52:00 | shift2 | 15:30:00 | 22:30:00
dbfiddle在这里
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句