-- with tab1 AS( select to_date('2011-03-01','yyyy-mm-dd')+level-1 today from dual connect by level <= to_date('2011-03-10','yyyy-mm-dd') - to_date('2011-03-01','yyyy-mm-dd')+1 ), tab2 as( select id,money,start_date,stop_date,money/(stop_date-start_date+1) avg_money from task ) select to_char(today,'yyyy-mm-dd') as date_, sum( case when today > stop_date then money else (today-start_date+1)*avg_money end) as t_money from tab1,tab2 where today between start_date and stop_date or today > stop_date group by today; --result:DATE_ T_MONEY ----------------------------------- 2011-03-01 4 2011-03-02 8 2011-03-03 15 2011-03-04 22 2011-03-05 29 2011-03-06 32 2011-03-07 32 2011-03-08 34 2011-03-09 36 2011-03-10 39
非常感谢gelyon,学习了,我昨天自己也想了一种,用笛卡尔积的方式,效率不如你的select sum(case) total_money,t_date from (select id,case when start_date > t_date then 0 when stop_date <= t_date then money when stop_date > t_date then money/(stop_date-start_date+1)*(t_date-start_date+1) end case, t_date from (select * from t_task t, (select to_date('2011-03-11','yyyy-MM-dd')-10+rownum-1 as t_date from user_objects where rownum <= 10) date_tb) all_tb ) sum_tb group by t_date order by t_date
--
with tab1 AS(
select to_date('2011-03-01','yyyy-mm-dd')+level-1 today from dual
connect by level <= to_date('2011-03-10','yyyy-mm-dd') - to_date('2011-03-01','yyyy-mm-dd')+1
),
tab2 as(
select id,money,start_date,stop_date,money/(stop_date-start_date+1) avg_money from task
)
select to_char(today,'yyyy-mm-dd') as date_,
sum(
case when today > stop_date
then money
else (today-start_date+1)*avg_money
end)
as t_money
from tab1,tab2
where today between start_date and stop_date
or today > stop_date
group by today; --result:DATE_ T_MONEY
-----------------------------------
2011-03-01 4
2011-03-02 8
2011-03-03 15
2011-03-04 22
2011-03-05 29
2011-03-06 32
2011-03-07 32
2011-03-08 34
2011-03-09 36
2011-03-10 39
from
(select id,case when start_date > t_date then 0
when stop_date <= t_date then money
when stop_date > t_date then money/(stop_date-start_date+1)*(t_date-start_date+1)
end case,
t_date
from
(select * from t_task t,
(select to_date('2011-03-11','yyyy-MM-dd')-10+rownum-1 as t_date from user_objects where rownum <= 10) date_tb) all_tb
) sum_tb
group by t_date
order by t_date