mysql - 是否可以减去 2 个子查询的值并返回差值？

Question

这背后的逻辑是我想从已售出的产品中减去退回的产品。这样我就可以销售净数量的产品。子查询返回数值，但我不确定如何从这些子查询中获取差异。

SELECT COUNT(QUANTITY_ORDERED) FROM PRODUCTS
WHERE 
(SELECT COUNT(QUANTITY_ORDERED) FROM PRODUCTS WHERE CATEGORY ='SOLD')-(SELECT COUNT(QUANTITY_ORDERED) FROM PRODUCTS WHERE CATEGORY='RETURNED')

Answer 1

只需执行条件 SUM() 而不是 COUNT()

SELECT product_id, 
       SUM(CASE WHEN CATEGORY ='SOLD' THEN QUANTITY_ORDERED ELSE 0 END)
     - SUM(CASE WHEN CATEGORY ='RETURNED' THEN QUANTITY_ORDERED ELSE 0 END) as total
FROM products
GROUP BY product_id

或

SELECT product_id, 
       COUNT(CASE WHEN CATEGORY ='SOLD' THEN 0 END)
     - COUNT(CASE WHEN CATEGORY ='RETURNED' THEN 0 END) as total
FROM products
GROUP BY product_id