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) ClockOutFROM tableNameWHERe EmpID = 456GROUP 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 ║╚═══════════╩═════════════╩═════════════╩═════════════╩═════════════╝
