根据余额的上一行更新总计 [英] Update the total based on the previous row of balance

问题描述

这是数据库数据.

Name   id  Col1  Col2  Col3 Col4 Total  Balance
Row1   1    6     1     A     Z     -      - 
Row2   2    2     3     B     Z     -      - 
Row3   3    9     5     B     Y     -      - 
Row4   4    16    8     C     Y     -      -

我想使用条件将总计"和余额"列从第2行更新为第4行.这是对总计列求和的逻辑:

I want to update the column "Total" and "Balance" from Row2 to Row4 with condition. This is the logic to sum the total column :

如果Col3 = A并且Col4 <> Z，则更新总计= Col1 + Col2 或
如果Col3 = B并且Col4 <> Z
，则总计= Col1-Col2 或
如果Col3 = C并且Col4 <> Z

update Total = Col1+Col2 if Col3 = A and Col4 <>Z
OR
Total = Col1-Col2 if Col3 = B and Col4 <>Z
OR
Total = Col1*Col2 if Col3 = C and Col4 <>Z

并更新余额，

余额=余额的上一行+总计的当前行

balance = previous row of balance + current row of total

推荐答案

以下是一个借助一个用户变量的解决方案.

Here comes a solution with assist of one user variable.

结果已验证并附带了完整的演示.

The result is verified with the full demo attached.

SQL:

-- data preparation for demo
create table tbl(Name char(100), id int, Col1 int, Col2 int, Col3 char(20), Col4 char(20), Total int, Balance int);
insert into tbl values
('Row1',1,6,1,'A','Z',0,0),
('Row2',2,2,3,'B','Z',0,0),
('Row3',3,9,5,'B','Y',0,0),
('Row4',4,12,8,'C','Y',0,0);
SELECT * FROM tbl;

-- Query needed
SET @bal = 0;
UPDATE tbl
SET
    Total = CASE    WHEN Col3 = 'A' and Col4 <> 'Z'
                        THEN Col1+Col2
                    WHEN Col3 = 'B' and Col4 <> 'Z'
                        THEN Col1-Col2
                    WHEN Col3 = 'C' and Col4 <> 'Z'
                        THEN Col1*Col2
                    ELSE 0 END,
    Balance = (@bal:=@bal + Total);
SELECT * FROM tbl;

输出(按预期):

mysql> SELECT * FROM tbl;
+------+------+------+------+------+------+-------+---------+
| Name | id   | Col1 | Col2 | Col3 | Col4 | Total | Balance |
+------+------+------+------+------+------+-------+---------+
| Row1 |    1 |    6 |    1 | A    | Z    |     0 |       0 |
| Row2 |    2 |    2 |    3 | B    | Z    |     0 |       0 |
| Row3 |    3 |    9 |    5 | B    | Y    |     0 |       0 |
| Row4 |    4 |   12 |    8 | C    | Y    |     0 |       0 |
+------+------+------+------+------+------+-------+---------+
4 rows in set (0.00 sec)

mysql> -- Query needed
mysql> SET @bal = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tbl
    -> SET
    ->     Total = CASE    WHEN Col3 = 'A' and Col4 <> 'Z'
    ->                         THEN Col1+Col2
    ->                     WHEN Col3 = 'B' and Col4 <> 'Z'
    ->                         THEN Col1-Col2
    ->                     WHEN Col3 = 'C' and Col4 <> 'Z'
    ->                         THEN Col1*Col2
    ->                     ELSE 0 END,
    ->     Balance = (@bal:=@bal + Total);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4  Changed: 2  Warnings: 0

mysql>
mysql> SELECT * FROM tbl;
+------+------+------+------+------+------+-------+---------+
| Name | id   | Col1 | Col2 | Col3 | Col4 | Total | Balance |
+------+------+------+------+------+------+-------+---------+
| Row1 |    1 |    6 |    1 | A    | Z    |     0 |       0 |
| Row2 |    2 |    2 |    3 | B    | Z    |     0 |       0 |
| Row3 |    3 |    9 |    5 | B    | Y    |     4 |       4 |
| Row4 |    4 |   12 |    8 | C    | Y    |    96 |     100 |
+------+------+------+------+------+------+-------+---------+
4 rows in set (0.00 sec)

这篇关于根据余额的上一行更新总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！