50分求sql高手写一条复杂的sql语句,会sql的都请进

[隔壁小王同学]

一张任务表,四个字段:任务号(id),任务成本(number),任务开始时间(start_date),任务结束时间(stop_date)
create table task (
id varchar2(32) primary key,
money number(10,2),
start_date date,
stop_date date
);

insert into task values(1,10,to_date('2011-03-01','yyyy-MM-dd'),to_date('2011-03-05','yyyy-MM-dd'));
insert into task values(2,12,to_date('2011-03-03','yyyy-MM-dd'),to_date('2011-03-06','yyyy-MM-dd'));
insert into task values(3,10,to_date('2011-03-10','yyyy-MM-dd'),to_date('2011-03-19','yyyy-MM-dd'));
insert into task values(4,20,to_date('2011-03-08','yyyy-MM-dd'),to_date('2011-03-17','yyyy-MM-dd'));
insert into task values(5,5,to_date('2011-03-23','yyyy-MM-dd'),to_date('2011-03-28','yyyy-MM-dd'));
insert into task values(6,10,to_date('2011-03-01','yyyy-MM-dd'),to_date('2011-03-05','yyyy-MM-dd'));
insert into task values(7,10,to_date('2011-04-02','yyyy-MM-dd'),to_date('2011-04-05','yyyy-MM-dd'));

现在需要查询出一个时间段内每天所有任务消耗的总成本,
限制条件:如果某个任务的开始时间不在这个时间段内则该任务不需要统计,
如果某个任务的结束时间早于时间段内的一天,则该任务在那天的成本为任务成本,
如果某个任务的结束时间晚于时间段内的一天,则该任务在那天的成本为任务平均成本*任务进行天数,即任务成本除以任务天数(任务结束时间与开始时间的差加一天)乘以已进行天数(那一天与开始时间的差加一天)

举例来说:
ID MONEY START_DATE STOP_DATE
1 10.00 2011/3/1 2011/3/5
2 12.00 2011/3/3 2011/3/6
3 10.00 2011/3/10 2011/3/19
4 20.00 2011/3/8 2011/3/17
5 5.00 2011/3/23 2011/3/28
6 10.00 2011/3/1 2011/3/5

比如我给定时间段是3月1日至3月10日,那么查询出的结果就应该是
date_, t_money
2011-03-01 4 1号任务和6号任务在此时间段内,都完成了一天,所以1号任务成本为2,6号成本为2, 合计为4
2011-03-02 8 1号任务和6号任务在此时间段内,1号任务成本为10/5*2=4, 6号任务成本为10/5*2=4,合计为8
2011-03-03 15 1,2,6号任务需统计, 1号成本为6, 2号成本为3, 6号成本为6, 合计为15
2011-03-04 22 1,2,6号任务需统计, 1号成本为8, 2号成本为6, 6号成本为8, 合计为22
2011-03-05 29 1,2,6号任务需统计, 1号成本为10, 2号成本为9, 6号成本为10, 合计为29
2011-03-06 32 1,2,6号任务需统计, 1号成本为10, 2号成本为12, 6号成本为10, 合计为32
2011-03-07 32 1,2,6号任务需统计, 1号成本为10, 2号成本为12, 6号成本为10, 合计为32
2011-03-08 34 1,2,4,6号任务需统计, 1号成本为10, 2号成本为12, 6号成本为10, 4号成本为2, 合计为34
2011-03-09 36 1,2,4,6号需统计, 1号成本为10, 2号成本为12, 6号成本为10, 4号成本为4, 合计36
2011-03-10 39 1,2,3,4,6号需统计, 1号成本为10, 2号成本为12, 6号成本为10, 3号成本为1, 4号成本为6, 合计39

如何能用一条复杂的sql语句查询出这个结果呢?求sql达人帮忙

[唐诗三百首]

请教楼主 "给定时间段是3月1日至3月10日"
为何"2011-03-10 --> 1,2,3,4,6号需统计"?
其中1号任务在2011-03-05就结束了.

[隔壁小王同学]

因为我需要统计的是每一天所有任务的总成本
比如给定时间是3月10日,则3月10日之前的结束的任务的成本都需要统计

[gelyon]

--
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