双表MySQL时间戳(需要计算百分比)

Question

我有两个表，我们称它们为 timeclock 和 time_tracker。

两个表都有 timestamps in 和 timestamps out。 time_tracker 仅在工作人员实际在特定应用程序中时创建一个“time_in”条目，然后在他们将工作标记为完成时创建一个“time_out”条目。

在给定的一天中，time_tracker 中将有多个这样的条目，而在 timeclock 中将只有一个 in，一个 out。

我正在尝试确定工作时间与打卡时间的对比，并以百分比形式显示。

到目前为止，我所做的工作一直有效，直到我加入时钟表。当我这样做时，似乎时间不知何故成倍增加，我无法确定故障所在。

查询如下:

SELECT it.user_id, SUM(TIMESTAMPDIFF(minute, it.time_in, it.time_out) / 60) as `Total Time Clocked In`,
SUM(TIMESTAMPDIFF(minute, isb.start_time, isb.end_time) / 60) as `Total Time in Work`
FROM time.time_track isb
INNER JOIN time.timeclock it ON isb.user_id = it.user_id
WHERE it.time_in >= '2013-10-01 00:00:00' AND it.time_out <= '2013-10-01 23:59:59'
AND isb.start_time >= '2013-10-01 00:00:00' AND isb.end_time <= '2013-10-01 23:59:59'
AND it.user_id = '12';

Answer 1

如果问题是您的“总计入时间”太高，这应该可以解决问题。从该表达式中删除 SUM 函数。

SELECT it.user_id, TIMESTAMPDIFF(minute, it.time_in, it.time_out) / 60 as `Total Time Clocked In`,
SUM(TIMESTAMPDIFF(minute, isb.start_time, isb.end_time) / 60) as `Total Time in Work`
FROM time.time_track isb
INNER JOIN time.timeclock it ON isb.user_id = it.user_id
WHERE it.time_in >= '2013-10-01 00:00:00' AND it.time_out <= '2013-10-01 23:59:59'
AND isb.start_time >= '2013-10-01 00:00:00' AND isb.end_time <= '2013-10-01 23:59:59'
AND it.user_id = '12';

编辑 - 我没有意识到特定日期的时钟表中可能有多个记录。以下语句应该适用于该场景。

SELECT  a.user_id, a.`Total Time Clocked In`, b.`Total Time in Work`

FROM

(
    SELECT  it.user_id, SUM(TIMESTAMPDIFF(minute, it.time_in, it.time_out)) / 60 as `Total Time Clocked In`

    FROM    time.timeclock it

    WHERE   it.time_in >= '2013-10-01 00:00:00' AND it.time_out <= '2013-10-01 23:59:59'
            AND it.user_id = '12'
) AS a

JOIN 

(
    SELECT  isb.user_id, SUM(TIMESTAMPDIFF(minute, isb.start_time, isb.end_time) / 60) as `Total Time in Work`

    FROM    time.time_track isb

    WHERE   isb.start_time >= '2013-10-01 00:00:00' AND isb.end_time <= '2013-10-01 23:59:59'
            AND isb.user_id = '12'

) AS b ON a.user_id = b.user_id