我也写个非主流的。。 select rq from( select a.rq,row_number()over(order by a.rq)rn from ( select trunc(sysdate)+rownum-1 rq from all_objects where rownum<(:days+10)*2)a, cg_noworkday b where a.rq=b.noworkday(+) and b.noworkday is null )where rn=:days+1:days部分用工作日比如10替换
startdate和enddate分别是这个期间的开始和结束日期 select (select enddate - startdate from dual) - (select count(noworkday) from cg_noworkday where noworkday between startdate and enddate) from dual
如果用函数的话 create or replace function func(days in number)return date as rq date:=trunc(sysdate)+days; v_count number:=days; begin while(v_count>0)loop select count(1) into v_count from cg_noworkday where noworkday>rq-v_count and noworkday<=rq; rq:=rq+v_count; end loop; return rq; end; 10个工作日后的时间 select func(10) from dual;
本人非主流的做法: select max(d1) from ( select c1.noworkday d1,c2.noworkday d2 from (select rownum+trunc(sysdate) noworkday from wf_element_field where rownum<20) c1 left join cg_workday c2 on c1.noworkday = c2.noworkday order by c1.noworkday ) where d2 is null and rownum<=10wf_element_field 是数据库中存在的一张表,且记录数大于20。10是表示1个工作日。
cg_noworkday表包含了包括周末在内的所有非工作日?
函数很容易做到,看看能不能用sql实现
select rq from(
select a.rq,row_number()over(order by a.rq)rn from
(
select trunc(sysdate)+rownum-1 rq
from all_objects
where rownum<(:days+10)*2)a,
cg_noworkday b
where a.rq=b.noworkday(+)
and b.noworkday is null
)where rn=:days+1:days部分用工作日比如10替换
select
(select enddate - startdate from dual) -
(select count(noworkday) from cg_noworkday where noworkday between startdate and enddate)
from dual
create or replace function func(days in number)return date
as
rq date:=trunc(sysdate)+days;
v_count number:=days;
begin
while(v_count>0)loop
select count(1) into v_count
from cg_noworkday
where noworkday>rq-v_count and noworkday<=rq;
rq:=rq+v_count;
end loop;
return rq;
end;
10个工作日后的时间
select func(10) from dual;
select max(d1)
from
(
select c1.noworkday d1,c2.noworkday d2 from
(select rownum+trunc(sysdate) noworkday from wf_element_field where rownum<20) c1
left join cg_workday c2 on c1.noworkday = c2.noworkday
order by c1.noworkday
)
where d2 is null and rownum<=10wf_element_field 是数据库中存在的一张表,且记录数大于20。10是表示1个工作日。