17,377
社区成员
发帖
与我相关
我的任务
分享
select a.id,a.dt,a.val,b.sumVal from (
select id,dt, val,TO_CHAR(TO_DATE(dt), 'YYYY/MM') as mon from test a
)a ,(
select id,sum(val) as sumVal,TO_CHAR(TO_DATE(dt), 'YYYY/MM') as mon from test group by TO_CHAR(TO_DATE(dt), 'YYYY/MM') ,id
)b
where a.id= b.id and a.mon = b.mon
order by a.id,a.dt;
--分析函数,sum(val) over (partition by to_char(dt,'yyyy-mm'))
SQL> with t as(
2 select 1 id,to_date('2011-01-01','yyyy-mm-dd') dt,50 val from dual union all
3 select 2,to_date('2011-01-05','yyyy-mm-dd'),20 from dual union all
4 select 3,to_date('2011-02-05','yyyy-mm-dd'),500 from dual union all
5 select 5,to_date('2011-02-09','yyyy-mm-dd'),100 from dual union all
6 select 6,to_date('2011-02-25','yyyy-mm-dd'),5050 from dual union all
7 select 7,to_date('2011-03-20','yyyy-mm-dd'),10000 from dual)
8 select id,dt,val,sum(val) over (partition by to_char(dt,'yyyy-mm')) sumval
9 from t
10 group by id,dt,val
11 /
ID DT VAL SUMVAL
---------- ----------- ---------- ----------
1 2011-01-01 50 70
2 2011-01-05 20 70
3 2011-02-05 500 5650
5 2011-02-09 100 5650
6 2011-02-25 5050 5650
7 2011-03-20 10000 10000
6 rows selected