mysql - 从 mysql 中创建的列中提取 timediff

Question

我正在处理 MySQL 查询以返回一些特定结果。我很亲近。现在，我需要计算两列之间的 TIMEDIFF。这是我当前的查询:

SELECT DATE_FORMAT(PunchDateTime, '%W') DAY, MAX(CASE WHEN PunchEvent = 'ClockIn' THEN DATE_FORMAT(PunchDateTime, '%r') END) AS 'Clock In', MAX(CASE WHEN PunchEvent = 'BreakOut' THEN 
    DATE_FORMAT(PunchDateTime, '%r') END) AS 'Break Out', MAX(CASE WHEN PunchEvent = 'BreakIn' THEN DATE_FORMAT(PunchDateTime, '%r') END) 'Break In', MAX(CASE WHEN PunchEvent = 'ClockOut' THEN 
    DATE_FORMAT(PunchDateTime, '%r') END) 'Clock Out', TIMEDIFF('Clock In', 'Break Out') AS Total FROM timeclock_punchlog WHERE EmpID = 456 GROUP BY 
    DATE_FORMAT(PunchDateTime, '%W') ORDER BY PunchDateTime;

现在，总计显示 00:00:00。我有什么想法可以做到这一点？谢谢迈克

Answer 1

经过大量谷歌搜索和修补，我弄明白了。我发现您不能在 TIMEDIFF 函数的查询中使用之前创建的别名。我基本上必须使用我用来创建别名的整个公式来代替 TIMEDIFF 函数中的别名。这是我的查询:

SELECT  DATE_FORMAT(PunchDateTime, '%W') DAY, MAX(CASE WHEN PunchEvent = 'ClockIn' THEN DATE_FORMAT(PunchDateTime, '%T') END) AS 'Clock In', MAX(CASE WHEN PunchEvent = 'BreakOut' THEN DATE_FORMAT(PunchDateTime, '%T') END) AS 'Break Out', MAX(CASE WHEN PunchEvent = 'BreakIn' THEN DATE_FORMAT(PunchDateTime, '%T') END) 'Break In', MAX(CASE WHEN PunchEvent = 'ClockOut' THEN DATE_FORMAT(PunchDateTime, '%T') END) 'Clock Out', (TIMEDIFF(MAX(CASE WHEN PunchEvent = 'clockout' THEN DATE_FORMAT(PunchDateTime, '%T') END), MAX(CASE WHEN PunchEvent = 'breakin' THEN DATE_FORMAT(PunchDateTime, '%T') END))) AS Total FROM timeclock_punchlog WHERE EmpID = 456 GROUP BY DATE_FORMAT(PunchDateTime, '%W') ORDER BY PunchDateTime;

我还必须将我的 %r 更改为 %T，以便它可以根据军事时间而不是标准的 AM/PM 时间正确计算 TIMEDIFF。希望这对某人有帮助!迈克