insert into RP_NE_ERR_TOTAL (RECEIVE_DATE,NE_DEVICE_ID,NE_DEVICE_NAME,NE_DEVICE_NAME)
select trunc(sysdate),nd.device_id,nd.name,count(1) from wo_ne_order wno,ne_device nd
where nd.device_id = wno.device_id
and wno.state_code in ('10E','10A','10W','10R','10RE','10M','10N')
and wno.create_date > trunc(sysdate)
and wno.finish_date > trunc(sysdate)
group by nd.device_id,nd.name;
insert into RP_NE_ERR_TOTAL (ERR_FINISH_CNT)
select count(1) from wo_ne_order wno,ne_device nd
where nd.device_id = wno.device_id
and wno.state_code in ('10M','10N')
and wno.finish_date > trunc(sysdate)
group by nd.device_id,nd.name;
insert into RP_NE_ERR_TOTAL (TWO_DATE_CNT)
select trunc(sysdate),nd.device_id,nd.name,count(1) from wo_ne_order wno,ne_device nd
where nd.device_id = wno.device_id
and wno.state_code in ('10M','10N')
and wno.create_date > trunc(sysdate -1)
and wno.create_datetrunc(sysdate)
group by nd.device_id,nd.name;
insert into RP_NE_ERR_TOTAL (THR_DATE_CNT)
select trunc(sysdate),nd.device_id,nd.name,count(1) from wo_ne_order wno,ne_device nd
where nd.device_id = wno.device_id
and wno.state_code in ('10M','10N')
and wno.create_date > trunc(sysdate-2)
and wno.create_datetrunc(sysdate)
group by nd.device_id,nd.name;
insert into RP_NE_ERR_TOTAL (ATH_DATE_CNT)
select trunc(sysdate),nd.device_id,nd.name,count(1) from wo_ne_order wno,ne_device nd
where nd.device_id = wno.device_id
and wno.state_code in ('10M','10N')
and wno.create_datetrunc(sysdate)
group by nd.device_id,nd.name;
insert into RP_NE_ERR_TOTAL (ERR_LEAVE_CNT)
select count(1) from wo_ne_order wno
where wno.state_code in ('10E','10A','10W','10R','10RE');
有高手能否将上述几个查询语句合并一下，我觉得这样效率有点点低。比如使用decode或者case when或者还有其他什么好方法。
|
