这是我的当前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;
有人可以帮我解决这个问题吗? 谢谢 Mike