mysql - 结合 SUM 和 WHERE - 没有得到我期望的结果

Question

SELECT shop.price,shop.item,shop.full_item_name,shop.qty,shop.shop_id,
SUM( averages_20128.combined_prices + averages_20127.combined_prices ) AS combined,
SUM( averages_20128.total_sales + averages_20127.total_sales ) AS total
FROM `shop`
JOIN `averages_20128`
ON averages_20128.full_item_name=shop.full_item_name
JOIN `averages_20127`
ON averages_20128.full_item_name=averages_20127.full_item_name
JOIN `theShops`
ON theShops.id=shop.shop_id
WHERE shop.price<combined/total
AND theShops.open='1'
AND shop.id!=''
AND `total`>10
ORDER BY combined/total DESC
LIMIT 100

错误:“where 子句”中的未知列“组合”

概述:我考虑过创建一个列来存储当前的平均值并对其进行排序，但是我刚开始时并没有这样做，现在有大量数据。我宁愿不经历这一切并重新计算。所以我希望 MySQL 可以帮我计算。

当我省略 WHERE/ORDER 子句时，这确实有效，让我相信我的语法有问题。

Answer 1

改变:

WHERE shop.price < combined/total

收件人:

WHERE shop.price <
  SUM(averages_20128.combined_prices + averages_20127.combined_prices)/total

或者将其移动到 HAVING 子句中:

HAVING shop.price < combined/total

WHERE 在 SUM() 之前应用，因此聚合列尚不可用。 HAVING 几乎最后出现。