mysql - 总结mysql中日期时间的多个datediff

Question

我有一张 table ，上面有一个用户和一天的打卡次数(打卡、突破、打卡、打卡)。现在假设用户休息 2 次或更多次。我需要总结所有休息时间的总和。我创建了一个 sqlfiddle，以便更轻松地展示我正在尝试做的事情。这是我的例子:http://sqlfiddle.com/#!2/21542/6现在我需要 (12:30:21 - 12:04:44) + (12:36:00 - 12:34:00) 来获得所有休息时间的总数。我怎样才能在我的查询中做到这一点。现在假设我的表中有 10 个用户和 10 天。我需要按日期和我认识的用户分组。

Answer 1

我首先要找到某种方法，将同一张表中的打卡记录与打卡记录联系起来。然后我们可以将这些数据放入一个表中，并用它来进行查询。

CREATE TEMPOARY TABLE breakPunchInOut ( 
    SELECT 
        DATE(punchout.PunchDateTime) AS ShiftDate,
        punchout.EmpId, 
        punchout.PunchId AS PunchOutID, 
        (SELECT 
            PunchId 
        FROM
            timeclock
        WHERE
            timeclock.EmpId = punchout.EmpId
        AND
            timeclock.`In-Out` = 1
        AND
            timeclock.PunchDateTime > punchout.PunchDateTime 
        AND
            DATE(timeclock.PunchDateTime) = DATE(punchout.PunchDateTime)
        ORDER BY
            timeclock.PunchDateTime ASC
        LIMIT 1
        ) AS PunchInID
    FROM
        timeclock AS punchout
    WHERE
        punchout.`In-Out` = 0
    HAVING
        PunchInID IS NOT NULL

);

此查询的工作方式是查找特定日期内的所有“打卡”，然后针对其中的每一个查找下一个发生在同一天、同一个人的“打卡”。 HAVING 子句过滤掉打卡后没有打卡的记录 - 所以可能是员工当天回家的地方。这是需要记住的一点，因为如果有人在轮类中途回家，那么他们的休息时间将不会添加到总数中。

需要指出的是，此方法仅适用于同一天开始和结束的类次。如果您有一个从晚上开始到第二天早上结束的夜类，那么您将不得不改变加入打卡时间和打卡时间的方式。

现在我们有了这个链接表，使用它为每个员工和每个类次创建汇总报告就相对简单了:

SELECT
    breakPunchInOut.ShiftDate,
    breakPunchInOut.EmpId,
    SUM(
      TIMESTAMPDIFF(MINUTE, punchOut.PunchDateTime, punchIn.PunchDateTime)
    ) AS TotalBreakLengthMins   
FROM
    breakPunchInOut
INNER JOIN
    timeclock AS punchOut
ON
    punchOut.PunchId = breakPunchInOut.PunchOutId
INNER JOIN
    timeclock AS punchIn
ON
    punchIn.PunchId = breakPunchInOut.PunchInId
GROUP BY
    breakPunchInOut.ShiftDate,
    breakPunchInOut.EmpId
;

注意我们使用的是 TIMESTAMPDIFF 函数，而不是 DATEDIFF。 DATEDIFF 仅计算两个日期之间的天数 - 它不用于时间。