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; 这个sql就是根据id查出每天的val和每个月sum出来的val。不知道能不能换种写法,因为查每天的val去检索了一次test表,查每个月sum的val又去检索了test 表。如果数据量大的话很消耗性能,这还是没有加条件的。如果在每个test表后面加上大量的条件,非常慢。能不能合并成一个???
sum(val)over(partition by id,dt)select t.id,t.dt,sum(t.val)over(partition by t.id,t.dt) sumVal
from test t
order by ...
--分析函数,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