mysql - 求和负整数给出错误的结果

Question

我的条件为负整数的 SUM 计算错误。

如果 CN (creditnote) 为真，则该行的总计必须设置为负数，然后将总计加在一起。

+----+-------+------------+-------+
| id | CN    | date       | total |
+----+-------+------------+-------+
| 1  | false | 2019-01-01 | 30    |
+----+-------+------------+-------+
| 2  | true  | 2019-01-01 | 15    |
+----+-------+------------+-------+

SELECT 
  MONTH(invoices.date) as month,
  IF(invoices.CN = true,
    CAST(SUM(DISTINCT COALESCE(invoices.total * -1, 0)) AS SIGNED),
    CAST(SUM(DISTINCT COALESCE(invoices.total, 0)) AS SIGNED)) AS total,
  from invoices
  GROUP BY month

结果

+-------+------+
| month | total|
+-------+------+
| 1     | -30  |
+-------+------+

我的预期:30 + (-15) = 15

+-------+------+
| month | total|
+-------+------+
| 1     |  15  |
+-------+------+

Answer 1

这符合您的想法:

SELECT
    MONTH(date) AS month,
    SUM(CASE WHEN CN = 'true' THEN -1.0 * total ELSE total END) AS total
FROM invoices
GROUP BY
    MONTH(date);

我说这很接近，因为不清楚 CN 如何或为什么会出现在聚合结果中。仅报告月份和总数对我来说很有意义。