2,668
社区成员
发帖
与我相关
我的任务
分享
--1.创建测试表
create table tmp as
select to_date('2017-01-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-01-05','yyyy-mm-dd') dtime, 200 money from dual union all
select to_date('2017-02-01','yyyy-mm-dd') dtime, 400 money from dual union all
select to_date('2017-02-08','yyyy-mm-dd') dtime, 500 money from dual union all
select to_date('2017-03-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-03-11','yyyy-mm-dd') dtime, 200 money from dual union all
select to_date('2017-03-21','yyyy-mm-dd') dtime, 300 money from dual union all
select to_date('2017-04-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-05-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-06-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-07-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-08-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-09-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-10-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-11-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-12-01','yyyy-mm-dd') dtime, 100 money from dual;
--2.SQL实现
select a.dtime,decode(b.dtime,null,a.money,b.money) money
from( select trunc(dtime,'mm') dtime,sum(money) money from tmp group by trunc(dtime,'mm')) a
left join(select dtime,money
from(select dtime,sum(money)over(partition by to_char(dtime,'q')) money,
row_number()over(partition by to_char(dtime,'q') order by trunc(dtime,'mm') desc) rn
from tmp
) where rn = 1
)b
on a.dtime = b.dtime