java - MySQL SUBTRACT 与 SUM 查询同一表的一列

Question

交易表:

+---------+--------+-------------+--------------+-----+
| DocType | SFCode | Productname | WarrantyCode | QTY |
+---------+--------+-------------+--------------+-----+
|   FP    | 12     | Item        |    1111-01   | 100 | -100
|   FP    | 12     | Item        |    2222-22   | 200 | 
|   FP    | 12     | Item        |    3333-33   | 350 | -350

|   LP    | 12     | Item        |    4444-44   | 10  |
|   LP    | 12     | Item        |    5555-55   | 20  |
|   LP    | 12     | Item        |    6666-66   | 35  | -35

|   CAS   | 12     | Item        |    1111-01   | 50  | -(50 Left, show)
|   CRS   | 12     | Item        |    3333-33   | 120 | -(230 Left, show)
|   CRS   | 12     | Item        |    6666-66   | 35  | -(0 Left, no show)

|   FPR   | 12     | Item        |    1111-01   | 10  | -(40 Left, show)
|   LPR   | 12     | Item        |    5555-55   | 20  | -(0 Left, no show)
|   CSR   | 12     | Item        |    1111-01   | 5   | -(50+5 Left, show)
|   CRR   | 12     | Item        |    6666-66   | 5   | -(Got back 5, show)
+---------+--------+-------------+--------------+-----|

关键:

FP: Foreign Purchase
LP: Local Purchase

CAS: Cash Sale
CRS: Credit Sale

FPR: Foreign Purchase Return
LPR: Local Purchase Return

CSR: Cash Sale Return
CRR: Credit Sale Return

有很多产品，但目前专注于单个 SFCode“12”。

QTY 是商店中存在的实物库存，DocType 是交易。

我需要对这张表做两件事。

1. 获取当前库存，即 (FP+LP+CSR+CRR) - (FPR+LPR+CAS+CRS) 注意:特定 DocType 可能没有交易

2. 获取特定保修代码尚未售完的产品的保修代码。从上到下查看表最后一列(未命名)，您就会明白。

请建议 Java-MySql 语句来帮助我实现此结果。如有任何帮助，我们将不胜感激。

Answer 1

对 #1 尝试这样的操作:

SELECT SFCode, SUM(FP+LP+CSR+CRR-FPR-LPR-CAS-CRS) AS Total FROM
(SELECT SFCode,
SUM(IF(DocType = "FP", QTY, 0)) AS FP,
>>please fill out all the columns<<    
FROM Transactions_Table
WHERE SFCode = "12"
GROUP BY DocType);

这是我的第二个尝试:(假设 SFCode 不是整数)

SELECT a.SFCode, a.WarrantyCode, (a.QTY-b.QTY) AS Stock FROM
(SELECT SFCode, WarrantyCode, QTY
FROM Transactions_Table
WHERE SFCode = "12"
AND DocType IN ('FP','LP','CSR','CRR')
GROUP BY WarrantyCode) AS a 
LEFT JOIN
(SELECT SFCode, WarrantyCode, QTY
FROM Transactions_Table
WHERE SFCode = "12"
AND DocType IN ('FPR','LPR','CAS','CRS')
GROUP BY WarrantyCode) AS b
ON a.SFCode = b.SFCode AND a.WarrantyCode = b.WarrantyCode;

我现在无法真正测试这一点，但这至少应该给您一个想法。