匹配条件的行之间的 MySQL 时间差

Question

您好，我需要有关 MySQL 数据库查询的帮助。

我有一个看起来像这样的表:

ID  TRACKID         DATE                    Name    Action
38  2013-08-12-36   2013-08-12 14:54:50 John Smith  0
37  2013-08-12-39   2013-08-12 14:54:28 John Smith  3
36  2013-08-12-39   2013-08-12 14:53:24 John Smith  4
35  2013-06-12-91   2013-08-12 14:30:01 John Smith  3
34  2013-06-12-91   2013-08-12 14:29:44 John Smith  4
31  2013-06-12-91   2013-08-12 14:28:39 John Smith  0

我想列出指定日期之间每个唯一 TRACKID 的 ACTION=0 和 ACTION=3 之间的时差。

SELECT DISTINCT TIMEDIFF
       (
                (select `date` from hesk_history where `action` = 0),
                (select `date` from hesk_history where `action` = 3 )
       ) AS DIFF

FROM    hesk_history

WHERE `date` BETWEEN '2013-08-12 00:00:00' AND '2013-08-12 23:59:59'

当每个 TRACKID 存在这些操作时，解决方案应列出每个不同的 TRACKID 和操作 0 和操作 3 之间的 TIMEDIFF。

TRACKID       DIFF
2013-06-12-91 -00:01:22

Answer 1

尝试像这样连接自己的表:

SELECT 
    t1.TRACKID,
    t2.date - t1.date
FROM
    table t1
    JOIN table t2 ON t2.TRACKID = t1.TRACKID AND t1.Action = 0 AND t2.Action = 3
WHERE t1.date BETWEEN '2013-08-12 00:00:00' AND '2013-08-12 23:59:59'