mysql 减法_如何从MYSQL中的其他列运行减法

I have sample Data :

Month Val Bval

Jan 2020 12000 0

Feb 2020 0

Mar 2020 100

Apr 2020 0

May 2020 500

Jun 2020 1000

I want to subtract the column values with beside value column . Need to get output like this :

Month Val Bval

Jan 2020 12000 0

Feb 2020 12000 0

Mar 2020 11900 100

Apr 2020 11900 0

May 2020 11400 500

Jun 2020 10400 1000

I have tried with Co related query :

SELECT t.Month,

t.Bval,

(SELECT x.val -t.Bval

FROM TABLE x

WHERE x.Month <= t.Month) AS Val

FROM TABLE t

ORDER BY t.Month

Not getting proper result .

Can any one suggest me the suitable way

解决方案

With a self join and aggregation:

select t1.Month, sum(t2.Val) - sum(t2.Bval) Val, t1.Bval

from tablename t1 left join tablename t2

on str_to_date(concat(t1.Month, ' 01'), '%b %Y %d') >= str_to_date(concat(t2.Month, ' 01'), '%b %Y %d')

group by t1.Month, t1.Val, t1.Bval

order by str_to_date(concat(t1.Month, ' 01'), '%b %Y %d')

See the demo.

Results:

> Month | Val | Bval

> :------- | ----: | ---:

> Jan 2020 | 12000 | 0

> Feb 2020 | 12000 | 0

> Mar 2020 | 11900 | 100

> Apr 2020 | 11900 | 0

> May 2020 | 11400 | 500

> Jun 2020 | 10400 | 1000