Oracle SQL中的组合查询

Question

我有一个名为Employess的表：

|id|datetime           |in-out|
|1 |2015-03-03 06:00:00|in    |
|1 |2015-03-03 14:15:00|out   |
|1 |2015-03-04 06:00:00|in    |
|1 |2015-03-04 15:00:00|out   |

我想创建一个视图，其中包含员工(id)的工作时长信息。

|id|datetime_in        |datetime_out       |how_log|
|1 |2015-03-03 06:00:00|2015-03-03 14:00:00|08:15  |
|1 |2015-03-04 06:00:00|2015-03-03 15:00:00|09:00  |

Could you help me?

- starko

Answer 1

如果你假设“in”和“out”交替出现，那么你只需获取下一个值：

select e.id, e.datetime as datetime_in, datetime_out,
       (floor((datetime_out - datetime_in)*24) || ':' ||
        mod(floor((datetime_out - datetime_in * 24*60), 60)
       ) as timediff
from (select e.*, lead(datetime) over (order by partition by id order by datetime) as datetime_out
      from employees e
     ) e
where in_out = 'in'

- Gordon Linoff

Answer 2

这里有另一种获取你想要结果的方法 - 使用pivot语句(适用于Oracle 11g及以上版本):

select id
     , to_char(in1, 'yyyy-mm-dd hh24:mi:ss')  as datetime_in
     , to_char(out1, 'yyyy-mm-dd hh24:mi:ss') as datetime_out
     , to_char(extract(hour from numtodsinterval(out1-in1, 'day'))
              , 'fm00') || ':' || 
       to_char(extract(minute from numtodsinterval(out1-in1, 'day'))
              , 'fm00')                       as how_long
  from ( select id
              , datetime
              , in_out 
              , row_number() over(partition by id, in_out
                                  order by datetime) as rn
          from tb1   
         order by datetime
      )
pivot (
  max(datetime) for in_out in ('in' as in1, 'out' as out1)
)
order by id, datetime_in

结果：

        ID DATETIME_IN         DATETIME_OUT        HOW_LONG
---------- ------------------- ------------------- --------
         1 2015-03-03 06:00:00 2015-03-03 14:15:00 08:15    
         1 2015-03-04 06:00:00 2015-03-04 15:00:00 09:00 

SQLFiddle演示

- Nick Krasnov

---

它运行得很好，但当表中有NULL值时，我遇到了问题。您可以看一下这个：http://sqlfiddle.com/#!4/f8db8/1/0 - starko

@starko 所以，一个员工有“进入”时间但没有“退出”时间（或者反过来）？在这种情况下，您期望什么结果？ - Nick Krasnov

@starko 然后你可以简单地使用 nvl() 或 coalesce() 函数来处理 null。这里是一个例子，如果 out 是 null，则“复制” in 时间。 - Nick Krasnov

谢谢你！你非常有帮助！ - starko