这是我当前的mysql表的样子:
PunchID EmpID PunchEvent PunchDateTime 1 0456 clockin 5/14/2013 8:36:26 AM 48 0456 breakout 5/14/2013 12:01:29 PM 53 0456 breakin 5/14/2013 12:28:31 PM 54 0456 clockout 5/14/2013 2:28:33 PM 57 0456 clockin 5/15/2013 7:38:34 AM 58 0456 breakout 5/15/2013 7:38:39 AM 59 0456 breakin 5/15/2013 7:38:41 AM 60 0456 clockout 5/15/2013 7:38:42 AM 现在,我想返回基于星期几进行分组的结果集,如下所示:
Day ClockIn BreakOut BreakIn ClockOut Tuesday 8:36:26 AM 12:01:29 PM 12:28:31 PM 2:28:33 PM Wednesday 7:38:34 AM etc, etc... 这是我当前的查询。但它只返回每天的第一打。
SELECT DATE_FORMAT(PunchDateTime, '%W') as day, PunchEvent, DATE_FORMAT(PunchDateTime, '%l:%m:%s %p') as time FROM timeclock_punchlog WHERE EmpID = '0456' GROUP BY DATE(PunchDateTime) ORDER BY PunchDateTime ASC; 有人可以帮我弄这个吗?谢谢迈克
SELECT DATE_FORMAT(PunchDateTime, '%W') DAY, MAX(CASE WHEN PunchEvent = 'ClockIn' THEN DATE_FORMAT(PunchDateTime, '%r') END) ClockIn, MAX(CASE WHEN PunchEvent = 'BreakOut' THEN DATE_FORMAT(PunchDateTime, '%r') END) BreakOut, MAX(CASE WHEN PunchEvent = 'BreakIn' THEN DATE_FORMAT(PunchDateTime, '%r') END) BreakIn, MAX(CASE WHEN PunchEvent = 'ClockOut' THEN DATE_FORMAT(PunchDateTime, '%r') END) ClockOut FROM tableName WHERE EmpID = 456 GROUP BY DATE_FORMAT(PunchDateTime, '%W') ORDER BY PunchDateTime SQLFiddle演示 输出值
╔═══════════╦═════════════╦═════════════╦═════════════╦═════════════╗ ║ DAY ║ CLOCKIN ║ BREAKOUT ║ BREAKIN ║ CLOCKOUT ║ ╠═══════════╬═════════════╬═════════════╬═════════════╬═════════════╣ ║ Tuesday ║ 08:36:26 AM ║ 12:01:29 PM ║ 12:28:31 PM ║ 02:28:33 PM ║ ║ Wednesday ║ 07:38:34 AM ║ 07:38:39 AM ║ 07:38:41 AM ║ 07:38:42 AM ║ ╚═══════════╩═════════════╩═════════════╩═════════════╩═════════════╝来源:stack overflow
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。