select tt1.id, run_time - nvl(stop_time, 0) from (select id, sum(endtime - createtime) run_time from t1 group by id) tt1, (select id, sum(endtime - createtime) stop_time from t2 group by id) tt2 where tt1.id = tt2.id(+)
作业题还是什么,这类需求最好在数据库中,执行效率比用类实现要快很多 再提供一种写法 select id, sum(run_time) from ( select id, endtime-createtime run_time from t1 union all select id, createtime-endtime run_time from t2 )group by id
你这边用的sum,是按照设备汇总的运行时间啊 我想计算,每个设备,每天 实际运行了多久
你的endtime是不是最晚到次日的0点?如果是的话可以这样写 select id, trunc(createtime),sum(run_time) from ( select id, endtime-createtime run_time from t1 union all select id, createtime-endtime run_time from t2 )group by id,trunc(createtime)
from (select id, sum(endtime - createtime) run_time from t1 group by id) tt1,
(select id, sum(endtime - createtime) stop_time from t2 group by id) tt2
where tt1.id = tt2.id(+)
TEST001 2014-12-01 08:00:00 2014-12-01 18:48:00
TEST002 2014-12-03 00:00:00 2014-12-04 00:00:00
TEST002 2014-12-02 07:00:00 2014-12-03 00:00:00
TEST002 2014-12-05 00:00:00 2014-12-05 07:30:00
TEST002 2014-12-04 00:00:00 2014-12-05 00:00:00
TEST003 2014-12-16 00:00:00 2014-12-17 00:00:00
TEST003 2014-12-17 00:00:00 2014-12-17 09:55:00
TEST003 2014-12-14 00:00:00 2014-12-15 00:00:00
TEST003 2014-12-13 00:00:00 2014-12-14 00:00:00
TEST003 2014-12-12 00:00:00 2014-12-13 00:00:00
TEST003 2014-12-11 00:00:00 2014-12-12 00:00:00
TEST003 2014-12-10 00:00:00 2014-12-11 00:00:00
TEST003 2014-12-09 00:00:00 2014-12-10 00:00:00
TEST003 2014-12-08 09:00:00 2014-12-09 00:00:00
TEST003 2014-12-15 00:00:00 2014-12-16 00:00:00
TEST004 2014-11-23 00:00:00 2014-11-24 00:00:00
TEST004 2014-11-22 00:00:00 2014-11-23 00:00:00
TEST004 2014-11-21 00:00:00 2014-11-22 00:00:00
TEST004 2014-11-20 09:00:00 2014-11-21 00:00:00
再提供一种写法
select id, sum(run_time)
from (
select id, endtime-createtime run_time from t1
union all
select id, createtime-endtime run_time from t2
)group by id
我想计算,每个设备,每天 实际运行了多久
select id, trunc(createtime),sum(run_time)
from (
select id, endtime-createtime run_time from t1
union all
select id, createtime-endtime run_time from t2
)group by id,trunc(createtime)