sql - Oracle SQL 中的组合查询

Question

我有表雇员:

|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   |

我想创建 View ，其中包含雇员 (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  |

你能帮帮我吗？

Answer 1

这是另一种获得结果的方法 - 使用 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 Demo