用lag函数取上月数据 然后当月累计数据减去上月数据就是单月数据了

我这是oracle 你看你啥数据库，用你数据库的语法给lag做个为空置0处理

可能你数据库数字型减空时恒为空

SELECT

    ID

    , YEAR

    , MONTH

    , AMOUNT - NVL(LAG(AMOUNT) OVER(ORDER BY YEAR, MONTH),0) AS AMOUNT_MONTH

FROM

    TABLE

select

a.YEAR,

a.MONTH,

(b.AMOUNT-a.AMOUNT) AS 当月数

from table1 a left join table2 b on a.YEAR=b.YEAR and a.MONTH=b.MONTH-1

select year,month,sum(数量) as amount from 表 group by year,month

(20条消息) SQL实现每月之前的金额累计_谦虚使人发胖的博客-CSDN博客

SELECT

a.years,a.months,sum(c.amount) amount

from

(

select 2021 as years, 01 as months , 10 as amount from dual

union all

select 2021 as years, 02 as months , 34 as amount from dual

union all

select 2021 as years, 03 as months , 26 as amount from dual

union all

select 2021 as years, 04 as months , 54 as amount from dual

) a 

left join 

(

SELECT

years,months,sum(amount)  as amount

from

(

select 2021 as years, 01 as months , 10 as amount from dual

union all

select 2021 as years, 02 as months , 34 as amount from dual

union all

select 2021 as years, 03 as months , 26 as amount from dual

union all

select 2021 as years, 04 as months , 54 as amount from dual

) b

group by years,months

) c

on a.years = c.years and a.months >= c.months

GROUP BY a.years,a.months

我自己造了一些数据，写法是这样的

select a.YEAR,a.MONTH,

       case when a.MONTH = 1 then a.AMOUNT 

else a.AMOUNT - b.AMOUNT end as AMOUNT

from fr_hys_sumbyjidu_test a

left join

    (select YEAR,MONTH+1 as MONTH,AMOUNT from fr_hys_sumbyjidu_test) b on a.MONTH=b.MONTH

这个有一月份数据

select 

t.month,

t.amount -t.amount2 

from (

select

t.month,

t.amount,

IF(t1.amount is null ,0,t1.amount)amount2 

from 

(

SELECT

id month,

SUM(cnt) amount

FROM

exam_test

WHERE

year = '2020' 

GROUP BY

month

)t

LEFT JOIN

(

SELECT

id+1 month,

SUM(cnt) amount

FROM

exam_test

WHERE

year = '2020' 

and id<='11'

GROUP BY

month

)t1

on t.month =t1.month

)t