17,086
社区成员
发帖
与我相关
我的任务
分享
日期 数量
2012-1-1 10
2012-2-5 20
2012-1-15 30
2012-3-1 100
2012-4-1 100
2012-4-12 40
2012-3-10 20
2012-4-21 50
2012-5-15 170
SELECT a.月份,
(SELECT SUM(b.数量)
FROM dt b
WHERE b.月份 <= a.月份) 数量
FROM dt a
GROUP BY a.月份
ORDER BY a.月份
select * from
(SELECT 月份,sum(数量)over(ORDER BY 月份) 数量
from (
select to_char(日期,'yyyy-mm') 月份,sum(数量) 数量
FROM T
GROUP BY to_char(日期,'yyyy-mm')
)
)where substr(月份,1,4)>='2012'
with t as
(select to_date('2012-01-01', 'yyyy-mm-dd') dt, 10 amt
from dual
union all
select to_date('2012-02-05', 'yyyy-mm-dd') dt, 20 amt
from dual
union all
select to_date('2012-01-15', 'yyyy-mm-dd') dt, 30 amt
from dual
union all
select to_date('2012-03-01', 'yyyy-mm-dd') dt, 100 amt
from dual
union all
select to_date('2012-04-01', 'yyyy-mm-dd') dt, 100 amt
from dual
union all
select to_date('2012-04-12', 'yyyy-mm-dd') dt, 40 amt
from dual
union all
select to_date('2012-03-10', 'yyyy-mm-dd') dt, 20 amt
from dual
union all
select to_date('2012-04-21', 'yyyy-mm-dd') dt, 50 amt
from dual
union all
select to_date('2012-05-15', 'yyyy-mm-dd') dt, 170 amt
from dual)
select t1.mth, sum(amt) over(order by mth)
from (SELECT to_char(dt, 'yyyymm') mth, sum(amt) amt
from t
group by to_char(dt, 'yyyymm')) t1
order by mth;
SELECT 月份,sum(数量)over(ORDER BY 月份) 数量
from (
select to_char(日期,'yyyy-mm') 月份,sum(数量) 数量
FROM T
GROUP BY to_char(日期,'yyyy-mm')
)
月份 数量
201201 40
201202 60
201203 220
201204 370
...