如何从MYSQL中的其他列运行减法 [英] How to do Running subtraction from other column in MYSQL

问题描述

我有示例数据:

Month        Val     Bval
Jan 2020   12000    0
Feb 2020            0
Mar 2020            100
Apr 2020            0
May 2020            500
Jun 2020           1000

我想用value列旁边的值减去列值.需要获得这样的输出:

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

我尝试了与Co相关的查询:

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

这篇关于如何从MYSQL中的其他列运行减法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！