我的SQL语句SUM()求和结果不符合预期，问题出在哪里？

嘿，我一眼就揪出你SQL语句里的问题了！问题出在WHERE子句的条件判断逻辑上，导致状态4的退款数据根本没被纳入计算，自然没法抵消已售出的数值。

1. 错误根源：= (1 or 4)的逻辑陷阱 #

你写的= (1 or 4)完全不是SQL里筛选多个状态值的正确写法。在SQL中，1 or 4会被当作布尔表达式求值：因为1和4都是非零值，所以这个表达式的结果是TRUE（在数值语境下等价于1）。

也就是说，你的条件实际上等价于= 1——这就解释了为什么加了or 4之后，结果和只筛选状态1时完全一致，状态4的退款记录根本没被选中！

2. 正确的多值筛选写法 #

要同时匹配状态1（已售出）和4（退款），有两种标准写法：

写法一：使用IN子句（推荐，简洁高效） #

SELECT ROUND(SUM(invoicetitle.unitpricegross*invoicetitle.suppliedquantity),2) as Costs, 
       SUM(invoicetitle.suppliedquantity) AS Unitamounts 
FROM invoicetitle 
WHERE (SELECT invoice.state 
       FROM invoice 
       WHERE invoicetitle.invoiceid = invoice.invoiceid 
         AND invoice.invoicedate >= 1609459200000 
         AND invoice.invoicedate <= 1640908800000) IN (1, 4) 
GROUP BY invoicetitle.invoicetitle_number

写法二：拆分OR条件 #

SELECT ROUND(SUM(invoicetitle.unitpricegross*invoicetitle.suppliedquantity),2) as Costs, 
       SUM(invoicetitle.suppliedquantity) AS Unitamounts 
FROM invoicetitle 
WHERE (SELECT invoice.state 
       FROM invoice 
       WHERE invoicetitle.invoiceid = invoice.invoiceid 
         AND invoice.invoicedate >= 1609459200000 
         AND invoice.invoicedate <= 1640908800000) = 1 
   OR (SELECT invoice.state 
       FROM invoice 
       WHERE invoicetitle.invoiceid = invoice.invoiceid 
         AND invoice.invoicedate >= 1609459200000 
         AND invoice.invoicedate <= 1640908800000) = 4 
GROUP BY invoicetitle.invoicetitle_number

3. 额外优化：用JOIN替代子查询（效率更高） #

你的原语句用了相关子查询，每次查询invoicetitle的一条记录都要查一次invoice表，效率偏低。可以改成JOIN写法，让查询更高效：

SELECT ROUND(SUM(it.unitpricegross * it.suppliedquantity), 2) AS Costs,
       SUM(it.suppliedquantity) AS Unitamounts
FROM invoicetitle it
JOIN invoice i ON it.invoiceid = i.invoiceid
WHERE i.invoicedate BETWEEN 1609459200000 AND 1640908800000
  AND i.state IN (1, 4)
GROUP BY it.invoicetitle_number

4. 结果验证 #

修正后，状态1的3281.10减去状态4的115.2正好是3165.9，单位数582减去32就是550，完全符合你预期的正确结果。

内容的提问来源于stack exchange，提问作者Sawb