mysql - 正确使用 Group By 功能

Question

我正在运行一个查询来找出我数据库中流动缓慢的股票，但它给出了一个错误，指出按条件分组的使用无效。逻辑是我想找出过去 120 天的总销售额小于该项目截至日期的总库存的 5% 的项目。以下是查询:-

SELECT 
  itemmaster.item as item, 
  stockbalance.qty as total_stock, 
  SUM(saleitems.saleqty) as total_sales 
FROM itemmaster 
JOIN stockbalance ON stockbalance.item = itemmaster.item 
JOIN saleitems ON saleitems.item = itemmaster.item 
WHERE saleitems.saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) AND NOW() 
  AND (SUM(saleitems.saleqty)) < (stockbalance.qty * 0.05)

Answer 1

您需要将求和条件从 WHERE 子句移动到 HAVING 子句。试试这个:

SELECT 
  itemmaster.item as item, 
  SUM(stockbalance.qty) as total_stock, 
  SUM(saleitems.saleqty) as total_sales 
FROM itemmaster 
JOIN stockbalance ON stockbalance.item = itemmaster.item 
JOIN saleitems ON saleitems.item = itemmaster.item 
WHERE 
    saleitems.saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) AND NOW()   
GROUP BY
    itemmaster.item
HAVING
    total_sales < (total_stock * 0.05)