mysql - 查找丢失的 MySQL 数据

Question

因此，我们有一个名为 timePunches 的表:

CREATE TABLE `timePunches` (
    `punchID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the punch',
    `employeeID` VARCHAR(50) NOT NULL COMMENT 'Who did the punch',
    `punchDATETIME` DATETIME NOT NULL COMMENT 'The time of the punch',
    `punchDTC_LINK` DATETIME NULL DEFAULT NULL COMMENT 'The previous start time for the OUT punch',
    `punchDATECRC` INT(100) NOT NULL COMMENT 'The punch CRC, to prevent hacking',
    `punchDIRECTION` TINYTEXT NOT NULL COMMENT 'What the punch did',
    `punchTOTAL` INT(11) NULL DEFAULT NULL,
    `fullName` TEXT NULL,
    PRIMARY KEY (`punchID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=15825

此表用于跟踪我们员工的打卡时间。也就是说，我们会跟踪他们何时进入 和何时退出。该表是一个审计合规表，这意味着我们不SELECT/UPDATE 记录，相反，我们只SELECT/INSERT 它们。

所以，这就是用户打卡时发生的情况:

• PUNCH IN: 应用程序发送 employeeID(字符串)、punchDATETIME(日期时间)、punchDATECRC (int) 和 punchDTC_LINK (DateTime [null]) 通过存储过程到数据库。此存储过程将必要的信息添加到数据库，包括触发内部函数以将用户的全名提取到同一个表中。

• PUNCH OUT:应用程序发送employeeID(字符串)、punchDATETIME(日期时间)、punchDATECRC (int) 和 punchDTC_LINK (DateTime) 到数据库。存储过程添加了必要的信息，包括在两个 DateTime 元素之间进行数学计算的触发器，并填写合法名称。

从上面我们可以看出，当用户点击 IN 时，查询看起来像这样:

INSERT INTO timePunches (punchID, employeeID, punchDATETIME, punchDTC_LINK, punchDATECRC, punchDIRECTION, punchTOTAL) VALUES (15797, 'prumple', '2012-01-11 17:35:10', NULL, -2011509138, 'IN', NULL);

而且，当用户打out时，它会发送如下内容:

INSERT INTO timePunches (punchID, employeeID, punchDATETIME, punchDTC_LINK, punchDATECRC, punchDIRECTION, punchTOTAL) VALUES (15797, 'prumple', '2012-01-11 19:39:52', '2012-01-11 17:35:10', -2011509138, 'OUT', NULL);

因此，正如我们所见，该用户的 DataTable 中有 2 个元素。一个 IN 和一个 OUT。

我需要做的是查明用户是否忘记打OUT。因此，假设用户打卡IN，整天工作，关闭他们的打卡时钟而不打卡，然后第二天再次打卡IN。然后，他完成了他的全类工作，并记得这次打卡 out。

现在，对于这种情况，我们有 2 个 IN 打洞，只有 1 个 OUT 打洞。我需要一种方法来检测这一点。

Answer 1

如果我理解正确，“IN”记录对应的“OUT”记录将在 employeeID(同一员工打卡)和“IN”DATETIME 将匹配“OUT”DTC_LINK。

-- Fetch all 'IN' records without a corresponding 'OUT'
    SELECT tp_in.*
     FROM timePunches tp_in
LEFT JOIN timePunches tp_out
          ON tp_in.employeeID = tp_out.employeeID
             AND
             tp_in.punchDATETIME = tp_out.punchDTC_LINK
    WHERE tp_in.punchDIRECTION = 'IN'
          AND
          tp_out.punchDTC_LINK IS NULL;