数据库模拟表如下
operate_id | operate_type | operate_date | process_sn |
1 | GD | 2013-09-15 17:18:37 | 10001 |
2 | JD | 2013-09-13 17:18:42 | 10001 |
3 | GD | 2013-09-19 17:18:42 | 10002 |
4 | JD | 2013-09-16 17:18:42 | 10002 |
查询出表中超时处理的单号数(即GD时间-JD时间>2个工作日的process_sn数)
ORACLE中时间段的工作日计算方法如下
select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date('2013-09-12','yyyy-MM-dd') - to_date('2013-09-01','yyyy-MM-dd')+1 )
where to_char( to_date('2013-09-01','yyyy-MM-dd')+rnum-1, 'D' )
not in ( '', '' )
直接运用在查询条件中报ORA-01841错误如下(oracle optimizer--查询转换)
select count(a.process_sn)
from tbl_operate_process a,tbl_operate_process b where a.process_sn=b.process_sn and a.operate_type='GD' and b.operate_type='JD'
and (select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date(substr(a.operate_date,0,10),'yyyy-MM-dd') - to_date(substr(b.operate_date,0,10),'yyyy-MM-dd')+1 )
where to_char( to_date(substr(b.operate_date,0,10),'yyyy-MM-dd')+rnum-1, 'D' )
not in ( '', '' ))>2
经过测试先建立function,再在查询条件中用function作为条件即可
create or replace function fun_workday(startdate in varchar2,enddate in varchar2)return number
IS
TOTALVALUE NUMBER;
begin
select count(*) into TOTALVALUE
from ( select rownum rnum
from all_objects
where rownum <= to_date(enddate,'yyyy-MM-dd') - to_date(startdate,'yyyy-MM-dd')+1 )
where to_char( to_date(startdate,'yyyy-MM-dd')+rnum-1, 'D' )
not in ( '', '' );
return TOTALVALUE;
end;
select count(a.process_sn)
from tbl_operate_process a,tbl_operate_process b
where a.process_sn=b.process_sn and a.operate_type='GD' and b.operate_type='JD' and
(select fun_workday(substr(b.operate_date,0,10),substr(a.operate_date,0,10)) from dual)>2