mysql - SQL:获得负值

我有这样的SQL：

SELECT  PRODUCTID, UCASE(MYTYPE) AS MYTYPE, UNIT,
       SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY ELSE 0 END) AS IN_QTY,
       SUM(CASE WHEN ADJUST_TYPE = 'DEDUCT' THEN REORDER_QTY ELSE 0 END) AS OUT_QTY,
       SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY
                WHEN ADJUST_TYPE = 'DEDUCT' THEN - REORDER_QTY
                ELSE 0
           END) AS TOTAL_BALANCE_QTY
FROM INVENTORY_HISTORY
GROUP BY PRODUCTID
ORDER BY MYTYPE ASC

它工作正常，直到我添加where子句，像这样：

WHERE STOREID='6' AND ENTERED_DATE BETWEEN '2014-09-15' AND '2014-09-30'

问题：

我将得到TOTAL_BALANCE_QTY的负值，因为它将所有IN_QTY求和并减去OUT_QTY，但IN_QTY的总和小于OUT_QTY。我如何避免负值？没有where子句，我没有问题，因为它从开始就将所有IN_QTY相加。有什么建议吗？



架构：

CREATE TABLE `inventory_history` (
  `INVHID` int(11) NOT NULL AUTO_INCREMENT,
  `INVENTORYID` int(11) DEFAULT NULL,
  `STOREID` int(11) DEFAULT NULL,
  `PRODUCTID` int(11) DEFAULT NULL,
  `UNIT` char(30) DEFAULT NULL,
  `QTY_BALANCE` decimal(10,2) DEFAULT NULL,
  `UNIT_PRICE` decimal(12,4) DEFAULT NULL,
  `REORDER_QTY` decimal(10,2) DEFAULT NULL,
  `MYTYPE` char(15) DEFAULT NULL,
  `ADJUST_TYPE` char(10) DEFAULT NULL,
  `TRANSC_ID` int(11) DEFAULT NULL,
  `ENTERED_BY` int(11) DEFAULT NULL,
  `ENTERED_DATE` datetime DEFAULT NULL,
  `MODIFIED_BY` int(11) DEFAULT NULL,
  `MODIFIED_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INVHID`)
) ENGINE=InnoDB AUTO_INCREMENT=2142 DEFAULT CHARSET=utf8

SQL数据：Dropbox Link（由于char 8000限制而无法放入sqlfiddle）

任何建议/帮助都很好。

最佳答案

SELECT PRODUCTID, MYTYPE, UNIT, IN_QTY, OUT_QTY, IF(TOTAL_BALANCE_QTY < 0, 0.00, TOTAL_BALANCE_QTY) AS TOTAL_BALANCE_QTY
FROM
(    SELECT PRODUCTID, UCASE(MYTYPE) AS MYTYPE, UNIT,
            SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY ELSE 0 END) AS IN_QTY,
            SUM(CASE WHEN ADJUST_TYPE = 'DEDUCT' THEN REORDER_QTY ELSE 0 END) AS OUT_QTY,
            SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY
                     WHEN ADJUST_TYPE = 'DEDUCT' THEN - REORDER_QTY
                     ELSE 0
                END
            ) AS TOTAL_BALANCE_QTY
    FROM INVENTORY_HISTORY
    GROUP BY PRODUCTID
    ORDER BY MYTYPE ASC
)temp

一个更好的工作示例是将GREATEST（）与默认值一起使用，因此您不需要外部查询

SELECT PRODUCTID, UCASE(MYTYPE) AS MYTYPE, UNIT,
       SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY ELSE 0 END) AS IN_QTY,
       SUM(CASE WHEN ADJUST_TYPE = 'DEDUCT' THEN REORDER_QTY ELSE 0 END) AS OUT_QTY,
       GREATEST(SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY
                         WHEN ADJUST_TYPE = 'DEDUCT' THEN - REORDER_QTY
                         ELSE 0
                    END), 0
       ) AS TOTAL_BALANCE_QTY
FROM INVENTORY_HISTORY
GROUP BY PRODUCTID
ORDER BY MYTYPE ASC

关于mysql - SQL:获得负值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26351683/