mysql - 比较两个数据库中的日期时间/时间戳值以进行员工调度 (MySQL)

Question

我的目标是将一个数据库中的员工日程表与另一个数据库中的实际时钟时间进行比较。

下面是我在搜索打卡数据库时简单地查找单个打卡时间的查询:

SELECT *
FROM `users_log`
WHERE `user` = 'Employee'
AND `type` = 'Login'
ORDER BY ABS(`logintime` - UNIX_TIMESTAMP(`scheduled_logintime_from_2nd_database`)))
LIMIT 1

不确定执行此操作的最佳方法(特别是因为在子查询中使用 ORDER BY 是不确定的)。但最终我想要某种父查询/联接，它将在员工姓名(user)上联接两个数据库，并显示两个数据库的计划登录时间和实际登录时间。

在上面的示例中，对于 scheduled_logintime_from_2nd_database 我只是使用文本字符串进行测试，最终这将是其他数据库中的实际列。 (注意，一个是时间戳，另一个是日期时间，因此是 UNIX_TIMESTAMP 函数)。

Answer 1

你们很接近。

我建议您按以下步骤操作:

首先，执行适当的 JOIN 操作将两个数据源收集在一起。像这样的事情:

      SELECT DATE(ul.logintime) day,
             ul.name, ul.logintime,
             UNIX_TIMESTAMP(sch.scheduled_logintime) sched_time
       FROM users_log ul
       JOIN schedule sch  ON ul.name = sch.name
                         AND DATE(ul.logintime) = DATE(sch.scheduled_logintime)

这应该会为您提供一堆行，每天和用户对应一个或多个行，显示计划时间和实际时间。

然后您可以将其用作子查询，也许可以执行如下操作:

 SELECT name, day,
        MAX(ABS(logintime - sched_time)),
        MIN(ABS(logintime - sched_time))
   FROM ( SELECT DATE(ul.logintime) day,
             ul.name, ul.logintime,
             UNIX_TIMESTAMP(sch.scheduled_logintime) sched_time
       FROM users_log ul
       JOIN schedule sch  ON ul.name = sch.name
                         AND DATE(ul.logintime) = DATE(sch.scheduled_logintime)
        )
  GROUP BY name, day

这将为您提供每个名字每天遵守时间表的最佳和最差情况。