每个CITEM对总数量的扣减计算剩余数

WITH TA AS
(SELECT CDF_NO, C_ITEM_CODE, LINE_ID, QTY, EXP_SUM_QTY
FROM FCCP855. V_RMA_INVENTRY
WHERE C_ITEM_CODE = 'CISA-C3KX-NM-1G=-002'
AND CDF_NO = '704028769'
ORDER BY LINE_ID DESC)

SELECT AA.*,
(CASE
WHEN REFER_QTY IS NULL OR REFER_QTY > QTY THEN
QTY
WHEN REFER_QTY < QTY AND REFER_QTY > 0 THEN
REFER_QTY
ELSE
0
END) REAL_OUT_QTY,
(CASE
WHEN (REFER_QTY IS NULL) OR (REFER_QTY > QTY) THEN
0
WHEN REFER_QTY < QTY AND REFER_QTY > 0 THEN
QTY - REFER_QTY
ELSE
QTY
END) REAL_LEFT_QTY
FROM (SELECT A.*,
(EXP_SUM_QTY - (SELECT SUM(QTY)
FROM TA
WHERE CDF_NO = A.CDF_NO
AND C_ITEM_CODE = A.C_ITEM_CODE
AND LINE_ID > A.LINE_ID)) REFER_QTY
FROM TA A) AA;
结果如下：

 

转载于:https://www.cnblogs.com/lookupme/p/5198085.html