SQL :基于字段类型的加减

Question

我有一张桌子，看上去如下：

tableA

ID   |   BALANCE   |   ACTION_QTY   |   ACTION_TYPE |
----------------------------------------------------
1    |     0       |       3        |       ADD     |
2    |     0       |       2        |       DEDUCT  | 
3    |     0       |       6        |       ADD     |      
4    |     0       |       3        |       ADD     | 
5    |     0       |       1        |       DEDUCT  | 
6    |     0       |       7        |       ADD     | 
7    |     0       |       8        |       DEDUCT  | 
8    |     0       |       3        |       ADD     | 
9    |     0       |       7        |       DEDUCT  |  
10   |     0       |       9        |       ADD     | 

SQL小提琴

如何基于ACTION_TYPE字段和ACTION_QTY字段对ACTION_TYPE字段进行更新

我想要的结果如下：

ID   |   BALANCE   |   ACTION_QTY   |   ACTION_TYPE |
----------------------------------------------------
1    |     3       |       3        |       ADD     |
2    |     1       |       2        |       DEDUCT  | 
3    |     7       |       6        |       ADD     |      
4    |     10      |       3        |       ADD     | 
5    |     9       |       1        |       DEDUCT  | 
6    |     16      |       7        |       ADD     | 
7    |     8       |       8        |       DEDUCT  | 
8    |     11      |       3        |       ADD     | 
9    |     4       |       7        |       DEDUCT  |  
10   |     13      |       9        |       ADD     | 

任何帮助都会很好。

Answer 1

您可以使用一个变量来保存累积金额：

SELECT ID, 
       @s := IF(ACTION_TYPE='ADD', @s + ACTION_QTY, @s - ACTION_QTY) AS BALANCE,
       ACTION_QTY,
       ACTION_TYPE
FROM tableA
CROSS JOIN (SELECT @s := 0) AS var
ORDER BY ID 

上面的查询假设只有两种类型的ACTION_TYPE值，即'ADD'和'DEDUCT'。因此，如果ACTION_TYPE不等于'ADD'，那么它就等于'DEDUCT'。

这里的演示