select to_char(A,'yyyymm') as A,sum(B)over(partition by to_char(A,'yyyymm')) from table
select sum(B),to_char(A,'yyyy-mm') as 'A; from tb group by a
---上边写错了 select sum(B),to_char(A,'yyyy-mm') as 'A' from tb group by a
-- 除了使用to_char()函数外,也可以使用trunc()函数将日期部分截断到月(取每月的第一天) select trunc(a,'mm') as a, sum(b) from tb group by trunc(a,'mm');-- trunc()函数应该比to_char()函数效率更高!(有待证明)
with dm_gw as( select to_date('2011-10-10', 'YYYY-MM-DD') A, '100' B from dual union all select to_date('2011-10-20', 'YYYY-MM-DD') A, '100' B from dual union all select to_date('2012-05-15', 'YYYY-MM-DD') A, '50' B from dual ) select distinct to_char(A,'yyyy-mm') as A,sum(B)over(partition by to_char(A,'yyyymm')) from dm_gw order by A--结果 2011-10 200 2012-05 50
select distinct to_char(A,'yyyy-mm') as A,sum(B)over(partition by to_char(A,'yyyymm')) from dm_gw order by A
to_char(A,'yyyymm') as Asum(B)
group by A
from table
from tb
group by a
select sum(B),to_char(A,'yyyy-mm') as 'A'
from tb
group by a
select trunc(a,'mm') as a, sum(b)
from tb
group by trunc(a,'mm');-- trunc()函数应该比to_char()函数效率更高!(有待证明)
with dm_gw as(
select to_date('2011-10-10', 'YYYY-MM-DD') A, '100' B from dual union all
select to_date('2011-10-20', 'YYYY-MM-DD') A, '100' B from dual union all
select to_date('2012-05-15', 'YYYY-MM-DD') A, '50' B from dual
) select distinct to_char(A,'yyyy-mm') as A,sum(B)over(partition by to_char(A,'yyyymm'))
from dm_gw order by A--结果
2011-10 200
2012-05 50
from dm_gw order by A