mysql - 使用来自另一个表的基于 MAX 的计算更新表

Question

我创建了一个表(出勤)来存储计算结果，我正在更新该表以填充大部分计算结果。我需要弄清楚员工 (EMP_NB) 当天何时下类。这是通过 TIMEDIFF(ADDTIME(PERIOD, '1:00'), SEC_TO_TIME(Activity_Secs)) 计算得出的。

我的问题是我想不出一种方法来获取每个 DATE 每个 EMP_NB 的 MAX(PERIOD)，同时还获取相应的 Activity_Secs。

表A

╭────────┬──────┬────────┬───────────────╮│ EMP_NB │ DATE │ Period │ Activity_Secs │╰────────┴──────┴────────┴───────────────╯

Here is the MySQL code I made to get the first period an employee logged in.

UPDATE calc_Attendance AS att, tableA AS a
JOIN (
        SELECT 
        EMP_NB,
        DATE,
        Period,
        Activity_Secs
        FROM tableA
        GROUP BY EMP_NB, DATE, Period
    ) m
ON a.EMP_NB = m.EMP_NB and a.DATE = m.DATE and a.Period = m.Period
SET DEPTARTURE_ACTUAL = TIMEDIFF(ADDTIME(m.Period, '1:00'), SEC_TO_TIME(m.Activity_Secs))
WHERE att.EMP_NB = m.EMP_NB AND att.DATE = m.DATE
;

任何帮助将不胜感激。我没有很多 SQL 经验，所以这让我发疯。

Answer 1

UPDATE calc_Attendance AS att, tableA AS a
JOIN (
        SELECT 
        EMP_NB,
        DATE,
        MAX(Period) AS Period,
        Activity_Secs
        FROM tableA
        GROUP BY EMP_NB,DATE,Activity_Secs
    ) m
ON a.EMP_NB = m.EMP_NB and a.DATE = m.DATE and a.Period = m.Period
SET DEPTARTURE_ACTUAL = TIMEDIFF(ADDTIME(m.Period, '1:00'), SEC_TO_TIME(m.Activity_Secs))
WHERE att.EMP_NB = m.EMP_NB AND att.DATE = m.DATE
;