17,086
社区成员
发帖
与我相关
我的任务
分享
select sum(
decode(sign(to_date('30-jun-14','dd-mm-yy')-end_Date),1,end_date, '30-jun-14')
-
decode(sign(go_date-to_date('1-jun-14','dd-mm-yy')),1,go_date, '1-jun-14')
+1
)
from statistics
where end_Date>='1-jun-14' and go_date<='30-jun-14' and name='a';
结果验证正确
with t as
(select 'a' name,
to_date('2014-06-20', 'yyyy-mm-dd') go_date,
to_date('2014-06-30', 'yyyy-mm-dd') end_date
from dual
union all
select 'a' name,
to_date('2014-05-28', 'yyyy-mm-dd') go_date,
to_date('2014-06-03', 'yyyy-mm-dd') end_date
from dual
union all
select 'a' name,
to_date('2014-06-28', 'yyyy-mm-dd') go_date,
to_date('2014-06-30', 'yyyy-mm-dd') end_date
from dual
union all
select 'a' name,
to_date('2014-05-29', 'yyyy-mm-dd') go_date,
to_date('2014-06-30', 'yyyy-mm-dd') end_date
from dual
union all
select 'b' name,
to_date('2014-05-29', 'yyyy-mm-dd') go_date,
to_date('2014-07-02', 'yyyy-mm-dd') end_date
from dual)
select name, count(*)
from (select name, go_date + level - 1 dt, rn
from (select t.*, rownum rn from t)
connect by go_date + level <= end_date + 1
and prior rn = rn
and prior dbms_random.value is not null)
where to_char(dt, 'mm') = '06'
group by rn, name;