计算工作时间两行之间的时间差 [英] Calculate the time difference between two rows for work time

问题描述

我有一张桌子:

timedate                 workclock
2018-01-01 09:00:00      check-in
2018-01-01 12:30:40      check-in
2018-01-02 09:00:00      check-in
2018-01-02 11:29:00      check-out
2018-01-03 14:29:00      check-out

我需要输出看起来像这样:

I need output to look like this:

date          checkin      checkout     working-time
2018-01-01    09:00        none         missing checkout
2018-01-02    09:00        11:29        02:29
2018-01-03    none         14:29        missing checkin

我无法加入行，所以任何帮助将不胜感激还有第三个 col 代表工人标签，但这应该很容易分组.

I'm unable to join the rows so any help will be appreciated There is a third col that represent the worker tag but this should be easy to group.

推荐答案

使用视图可以简化查询，所以使用类似

Using views simplifies the queries, so use something like

create view in_gate_times as
select date(date_time) xdate, time(date_time) xtime
  from gate_times where gate_op = 'check-in';

create view out_gate_times as
select date(date_time) xdate, time(date_time) xtime
  from gate_times where gate_op = 'check-out';

然后使用以下查询

select i.xdate, i.xtime, ifnull(o.xtime, 'missing')
  from in_gate_times i
  left join out_gate_times o on i.xdate = o.xdate
 union
select o.xdate, ifnull(i.xtime, 'missing'), o.xtime
  from in_gate_times i
 right join out_gate_times o on i.xdate = o.xdate
 order by 1, 2, 3

在 SQLFiddle

如果使用 union 使您的查询变慢，请使用 union all 并进行以下更改

If using union made your query slow, use union all with following change

select i.xdate, i.xtime, ifnull(o.xtime, 'missing')
  from in_gate_times i
  left join out_gate_times o on i.xdate = o.xdate
 union all
select o.xdate, ifnull(i.xtime, 'missing'), o.xtime
  from in_gate_times i
 right join out_gate_times o on i.xdate = o.xdate
 where i.xdate is null
 order by 1, 2, 3

在 SQLFiddle

如果视图被禁止，只需用它的查询替换每个视图，所以最后一个查询将是

If views are forbidden just replace each view with its query, so the last query will be

select i.xdate, i.xtime, ifnull(o.xtime, 'missing')
  from (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-in') i
  left join (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-out') o
    on i.xdate = o.xdate

union all

select o.xdate, ifnull(i.xtime, 'missing'), o.xtime
  from (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-in') i
 right join (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-out') o
    on i.xdate = o.xdate
 where i.xdate is null
 order by 1, 2, 3

可以在SQLFiddle

这篇关于计算工作时间两行之间的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！