开发者社区> 问答> 正文

如何将mysql表行转置为列?mysql

这是我当前的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; 有人可以帮我弄这个吗?谢谢迈克

展开
收起
保持可爱mmm 2020-05-17 17:36:46 540 0
1 条回答
写回答
取消 提交回答
  • 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

    2020-05-17 17:41:57
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
搭建电商项目架构连接MySQL 立即下载
搭建4层电商项目架构,实战连接MySQL 立即下载
PolarDB MySQL引擎重磅功能及产品能力盛大发布 立即下载

相关镜像