mysql查询余额变化,mysql-根据上一行余额更新总数

这是数据库数据.

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.这是合计总计列的逻辑：

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

解决方法:

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

附带完整的演示来验证结果.

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)

标签：sql,mysql

来源： https://codeday.me/bug/20191012/1900237.html