解决方案 »
- oracle可以在查询中更新数据吗?
- 菜鸟求解在oracle中(2**32)的意思
- --------------BFILE 大家用的多么?-------------
- 关于PLSQL导入数据的问题 。。。。。。。。。~~~~。。。~。~。~。
- 求一类比功能:先SQL Server 2000中有OSQL.EXE文件,不知道Oracle 9.i里有没有这样的功能?
- 如何更改数据库链接访问用户?
- 这里有使用Oracle 10g的吗?帮我看看这个问题!
- 菜鸟问题:建立服务出错
- 怎么取出一个表中(30个字段)前5个值最大的字段?请高手赐教!!!在线等~~~~~~~~
- Delphi+oracle 的安装问题????急急急!!!
- sql问题
- pl/sql delete 多条数据时,报 ora-00936?
--
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