mysql - 如何进行 SQL 查询以返回 checkin 和 checkout 之间的时间差？

Question

我正在使用 mysql，我有一个类似于这个的表:

id  | user  | task | time                  | checkout
----+-------+------+-----------------------+---------
1   | 1     | 1    | 2014-11-25 17:00:00   | 0
2   | 2     | 2    | 2014-11-25 17:00:00   | 0
3   | 1     | 1    | 2014-11-25 18:00:00   | 1
4   | 1     | 2    | 2014-11-25 19:00:00   | 0
5   | 2     | 2    | 2014-11-25 20:00:00   | 1
6   | 1     | 2    | 2014-11-25 21:00:00   | 1
7   | 1     | 1    | 2014-11-25 21:00:00   | 0
8   | 1     | 1    | 2014-11-25 22:00:00   | 1

id 只是一个自动生成的主键，如果该行注册了一个用户 checkin ，则 checkout 为 0，如果用户正在从任务中 checkout ，则为 1。

我想知道如何做一个查询，返回用户在每个任务上花费了多少时间，也就是说，我想知道checkout=0时间和最近的时间差的总和checkout=每个用户和任务 1 次。

编辑:为了让事情更清楚，我期望从我的查询中得到的结果是:

user  | task | SUM(timedifference)
------+------+-----------------
1     | 1    | 02:00:00
1     | 2    | 02:00:00
2     | 2    | 03:00:00

我试过使用 SUM(UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP(time))，同时按用户和任务分组来计算已经过了多少时间，但我不知道如何使查询只对我想要的特定时间之间的差异求和，而不是对所有时间求和。

有人可以帮忙吗？这有可能吗？

Answer 1

正如所有评论告诉您的那样，您当前的表结构并不理想。但是，仍然可以将 checkin 与 checkout 配对。这是一个 SQL 服务器实现，但我相信您可以将它转换为 MySql:

SELECT id
    , user_id
    , task
    , minutes_per_each_task_instance = DATEDIFF(minute, time, (
            SELECT TOP 1 time
            FROM test AS checkout
            WHERE checkin.user_id = checkout.user_id 
                AND checkin.task = checkout.task 
                AND checkin.id < checkout.id 
                AND checkout.checkout = 1
            ))
FROM test AS checkin
WHERE checkin.checkout = 0

上面的代码可以工作，但是随着你的表开始增长，它会变得越来越慢。几十万之后就会变得明显

我建议将 time 列重命名为 checkin 而不是将 checkout bool 字段设为日期时间，并在用户结帐时更新记录。这样您将拥有一半的记录数，并且没有用于读取或查询的复杂逻辑