with t as
(select 'a' nm, '20140707' dt, '8:30:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140707' dt, '9:30:22' tm, 'output' typ
from dual
union all
select 'a' nm, '20140707' dt, '10:30:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140707' dt, '12:30:20' tm, 'output' typ
from dual
union all
select 'a' nm, '20140708' dt, '8:14:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140708' dt, '12:30:20' tm, 'output' typ
from dual)
select nm,
dt,
tm input,
to_char(times, 'hh24:mi:ss') output,
round((times2 - times) * 24, 2) work_time
from (select t1.*,
(select min(to_date(t2.dt || ' ' || t2.tm,
'yyyymmdd hh24:mi:ss'))
from t t2
where t1.times <=
to_date(t2.dt || ' ' || t2.tm, 'yyyymmdd hh24:mi:ss')
and t2.typ = 'output') times2
from (select t.*,
to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss') times
from t
where t.typ = 'input'
order by to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss')) t1);
with t as
(select 'a' nm, '20140707' dt, '8:30:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140707' dt, '9:30:22' tm, 'output' typ
from dual
union all
select 'a' nm, '20140707' dt, '10:30:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140707' dt, '12:30:20' tm, 'output' typ
from dual
union all
select 'a' nm, '20140708' dt, '8:14:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140708' dt, '12:30:20' tm, 'output' typ
from dual)
select nm,
dt,
tm input,
to_char(times, 'hh24:mi:ss') output,
round((times2 - times) * 24, 2) work_time
from (select t1.*,
(select min(to_date(t2.dt || ' ' || t2.tm,
'yyyymmdd hh24:mi:ss'))
from t t2
where t1.times <=
to_date(t2.dt || ' ' || t2.tm, 'yyyymmdd hh24:mi:ss')
and t2.typ = 'output') times2
from (select t.*,
to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss') times
from t
where t.typ = 'input'
order by to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss')) t1);
如果每条都如此查询那必须知道数据那就没意义了,我需要的是从数据库中查出的。上面的几条是我举得例子如果一天不止这几条也不知道员工谁。那要如何
with t as
(select 'a' nm, '20140707' dt, '8:30:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140707' dt, '9:30:22' tm, 'output' typ
from dual
union all
select 'a' nm, '20140707' dt, '10:30:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140707' dt, '12:30:20' tm, 'output' typ
from dual
union all
select 'a' nm, '20140708' dt, '8:14:20' tm, 'input' typ
from dual
union all
select 'a' nm, '20140708' dt, '12:30:20' tm, 'output' typ
from dual)
select nm,
dt,
tm input,
to_char(times, 'hh24:mi:ss') output,
round((times2 - times) * 24, 2) work_time
from (select t1.*,
(select min(to_date(t2.dt || ' ' || t2.tm,
'yyyymmdd hh24:mi:ss'))
from t t2
where t1.times <=
to_date(t2.dt || ' ' || t2.tm, 'yyyymmdd hh24:mi:ss')
and t2.typ = 'output') times2
from (select t.*,
to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss') times
from t
where t.typ = 'input'
order by to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss')) t1);
如果每条都如此查询那必须知道数据那就没意义了,我需要的是从数据库中查出的。上面的几条是我举得例子如果一天不止这几条也不知道员工谁。那要如何
上面的数据只是我的构建的一个临时的测试数据表,你可以忽略掉,直接用下面的SQL语句,表名为T。select nm,
dt,
tm input,
to_char(times, 'hh24:mi:ss') output,
round((times2 - times) * 24, 2) work_time
from (select t1.*,
(select min(to_date(t2.dt || ' ' || t2.tm,
'yyyymmdd hh24:mi:ss'))
from t t2
where t1.times <=
to_date(t2.dt || ' ' || t2.tm, 'yyyymmdd hh24:mi:ss')
and t2.typ = 'output') times2
from (select t.*,
to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss') times
from t
where t.typ = 'input'
order by to_date(dt || ' ' || tm, 'yyyymmdd hh24:mi:ss')) t1);