在Laravel中使用MySql实现考勤报表
提问于 2021-09-24 22:34:13
日安！
我想生成一个报告，将显示在附件中的照片一样。Click this example photo
我想在查询中将datetime分为时间(上午7:00AM)、超时AM (12:00AM)、下午时间(12:30 AM )和超时PM((5:00 PM
到目前为止，我得到的只是一个静态日期，我不知道如何通过使用timerange Click the example photo 2来使其动态。希望你能帮上忙。谢谢。
select datetime,id,
(CASE WHEN datetime > '19/04/2021 6:30' AND datetime < '19/04/2021 8:30' THEN datetime ELSE NULL END) as TimeInAM,
(CASE WHEN datetime > '19/04/2021 11:30' AND datetime < '19/04/2021 12:30' THEN datetime ELSE NULL END) as TimeOutAM,
(CASE WHEN datetime > '19/04/2021 12:30' AND datetime < '19/04/2021 13:30'THEN datetime ELSE NULL END) as TimeInPM,
(CASE WHEN datetime > '19/04/2021 16:30' AND datetime < '19/04/2021 21:30'THEN datetime ELSE NULL END) as TimeOutPM
from time_attendances WHERE id = 1345
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-09-24 23:50:47
我建议不要使用保留的MySQL单词，比如datetime：https://dev.mysql.com/doc/refman/8.0/en/keywords.html
你是不是在尝试这样的东西：
SELECT d,
if( DATE_FORMAT(d,'%r') between '06:30:00 AM' and '08:30:00 AM' , d, 'Null' ) as TimeInAM ,
if( DATE_FORMAT(d,'%r') between '11:30:00 AM' and '12:29:59 AM' , d, 'Null' ) as TimeOutAM ,
if( DATE_FORMAT(d,'%r') between '12:30:00 AM' and '01:30:00 PM' , d, 'Null' ) as TimeInPM ,
if( DATE_FORMAT(d,'%r') between '04:30:00 PM' and '09:30:00 AM' , d, 'Null' ) as TimeOutPM
from test ;演示：https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/62
07:01:00 AM格式的DATE_FORMAT(date_time,'%r')返回日期，09:30:00 PM格式。
请根据您的值进行检查，现在就让我检查。
编辑：https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/67
SELECT d,dayname(d) as week_day,
if( DATE_FORMAT(d,'%r') between '06:30:00 AM' and '08:30:00 AM' , DATE_FORMAT(d,"%h:%i %p"), 'Null' ) as TimeInAM ,
if( DATE_FORMAT(d,'%r') between '11:30:00 AM' and '12:29:59 AM' , DATE_FORMAT(d,"%h:%i %p"), 'Null' ) as TimeOutAM ,
if( DATE_FORMAT(d,'%r') between '12:30:00 AM' and '01:30:00 PM' , DATE_FORMAT(d,"%h:%i %p"), 'Null' ) as TimeInPM ,
if( DATE_FORMAT(d,'%r') between '04:30:00 PM' and '09:30:00 PM' , DATE_FORMAT(d,"%h:%i %p"), 'Null' ) as TimeOutPM
from test order by d desc ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69323725
复制相关文章
腾讯云开发者
