SQL : 添加 &基于字段类型的减号 [英] SQL : ADD & MINUS based on Field type

问题描述

我有一张如下所示的表格:

I have a table that look like below:

表A

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 FIDDLE

如何根据 ACTION_TYPE 字段和 ACTION_QTY 字段的总和和扣除来更新 BALANCE 字段

How do i update the BALANCE field based on sum and deduct of ACTION_TYPE field and ACTION_QTY field

我想要的结果如下:

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     | 

任何帮助都会很棒.

推荐答案

可以使用一个变量来保存累计金额:

You can use a variable to hold the cumulative amount:

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'.

The above query assumes that there are only two types of ACTION_TYPE values, namely 'ADD' and 'DEDUCT'. Hence, if ACTION_TYPE is not equal to 'ADD', then it is equal to 'DEDUCT'.

此处演示

这篇关于SQL : 添加 &基于字段类型的减号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！