MySQL 比较日期时间

Question

我在数据库MySQL中有这个数据表。

+---------------------+---------------------+--------+----+
| Start___Date        | End___Date          | Coding | ID |
+---------------------+---------------------+--------+----+
| 2014-12-16 18:08:51 | 2014-12-16 20:00:16 | 7605   | 1  |
| 2014-12-16 22:01:39 | 2014-12-16 23:36:36 | 9905   | 2  |
| 2014-12-16 22:45:00 | 2014-12-16 23:36:36 | 9905   | 3  |
| 2014-12-16 23:18:32 | 2014-12-16 23:19:04 | 9905   | 4  |
+---------------------+---------------------+--------+----+

在此表中，我有三个相同的编码:9905。

当开始日期后一小时内重复相同的编码时，我需要通过一个查询删除所有行。

在这种情况下，我需要取消第三行。

+---------------------+---------------------+--------+----+
| Start___Date        | End___Date          | Coding | ID |
+---------------------+---------------------+--------+----+
| 2014-12-16 18:08:51 | 2014-12-16 20:00:16 | 7605   | 1  |
| 2014-12-16 22:01:39 | 2014-12-16 23:36:36 | 9905   | 2  |
| 2014-12-16 23:18:32 | 2014-12-16 23:19:04 | 9905   | 4  |
+---------------------+---------------------+--------+----+

因为与开始时间(22:01)相比，同一编码9905在一小时内两次被记录在表中，并且由于开始时间(23:18)比开始时间大，所以用编码9905保存记录号4开始时间(22:01)，编码相同。

你能帮我吗？预先感谢您。

编辑#1

我尝试过这个查询 JOIN 但输出是错误的:

mysql> SELECT
    CB.Start___Date,
    CB.End___Date,
    CB.Coding
FROM
    doTable AS CB
JOIN doTable AS CD ON (CB.Coding = CD.Coding)
WHERE
    TIME_TO_SEC(
        TIMEDIFF(
            CB.Start___Date,
            CD.Start___Date
        )
    ) / 60 > 60
GROUP BY
    CB.Coding;
+---------------------+---------------------+--------+
| Start___Date        | End___Date          | Coding |
+---------------------+---------------------+--------+
| 2014-12-16 23:18:32 | 2014-12-16 23:19:04 |   9905 |
+---------------------+---------------------+--------+
1 row in set

Answer 1

这是我针对您的问题的解决方案，祝您编码愉快!

mysql> DROP TABLE
IF EXISTS `doTable1`;

CREATE TABLE `doTable1` (
    `coding` INT (10) DEFAULT NULL,
    `Start___Date` datetime DEFAULT NULL,
    `id` INT (10) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) ENGINE = MyISAM;

INSERT INTO `doTable1` (coding, Start___Date)(
    SELECT
        coding,
        MIN(Start___Date)
    FROM
        doTable
    GROUP BY
        coding
);

SELECT
    a.*, b.*, TIMEDIFF(
        a.Start___Date,
        b.Start___Date
    ) AS diff
FROM
    doTable a
JOIN `doTable1` b ON a.coding = b.coding
WHERE
    (
        TIMEDIFF(
            a.Start___Date,
            b.Start___Date
        ) = '00:00:00'
        OR TIMEDIFF(
            a.Start___Date,
            b.Start___Date
        ) >= '01:01:01'
    );
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

+--------+---------------------+---------------------+----+--------+---------------------+----+----------+
| Coding | End___Date          | Start___Date        | ID | coding | Start___Date        | id | diff     |
+--------+---------------------+---------------------+----+--------+---------------------+----+----------+
|   7605 | 2014-12-16 20:00:16 | 2014-12-16 18:08:51 |  1 |   7605 | 2014-12-16 18:08:51 |  1 | 00:00:00 |
|   9905 | 2014-12-16 23:36:36 | 2014-12-16 22:01:39 |  2 |   9905 | 2014-12-16 22:01:39 |  2 | 00:00:00 |
|   9905 | 2014-12-16 23:19:04 | 2014-12-16 23:18:32 |  4 |   9905 | 2014-12-16 22:01:39 |  2 | 01:16:53 |
+--------+---------------------+---------------------+----+--------+---------------------+----+----------+
3 rows in set