我们先看下我们的sql语句
select wpd.*,wp.* from t_xg_wp wp
left join t_xg_wp_detail wpd on wp.workpackage_id = wpd.workpackage_id
left join t_xg_wp_skill sk on wp.skill_id = sk.skill_id
where wp.is_delete = '0' and sk.is_delete = '0' and
(DATE_FORMAT(wpd.start_time,'%H:%i')<'8:00' and DATE_FORMAT(wpd.end_time,'%H:%i')>'10:30' and
wpd.datetime = '2019-04-20' and wp.skill_id = '3')
执行效果如下
下面我们改一下上面的sql之后
select wpd.*,wp.* from t_xg_wp wp
left join t_xg_wp_detail wpd on wp.workpackage_id = wpd.workpackage_id
left join t_xg_wp_skill sk on wp.skill_id = sk.skill_id
where wp.is_delete = '0' and sk.is_delete = '0' and
(DATE_FORMAT(wpd.start_time,'%H:%i')<'08:00' and DATE_FORMAT(wpd.end_time,'%H:%i')>'10:30' and
wpd.datetime = '2019-04-20' and wp.skill_id = '3')
相信个位看到这里已经看到了区别了,没错,这里的时间‘08:00’和'8:00'和时间的比较是不相同的,这里就提醒我们需要把这个字符串修改成时间date类型的值,再进行比较。
如下就可以了。
select str_to_date('08:00', '%H:%i');