本文介绍了SQL - SUM() 上的 WHERE 条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否可以这样做:
SELECT
`e`.*,
`rt`.`review_id`,
(SUM(vt.percent) / COUNT(vt.percent)) AS rating
FROM `catalog_product_entity` AS `e`
INNER JOIN `rating_option_vote` AS `vt`
ON vt.review_id = e.review_id
WHERE (rating >= '0')
GROUP BY `vt`.`review_id`
我特别想在除法结果值上加上 where 条件
In particular I would like to put a where condition on the division result value
推荐答案
这可以通过 HAVING 子句来完成:
This can be accomplished with a HAVING clause:
SELECT e.*, rt.review_id, (SUM(vt.percent) / COUNT(vt.percent)) AS rating
FROM catalog_product_entity AS e
INNER JOIN rating_option_vote AS vt ON e.review_id = vt.review_id
GROUP BY vt.review_id
HAVING (SUM(vt.percent) / COUNT(vt.percent)) >= 0
ORDER BY (SUM(vt.percent) / COUNT(vt.percent)) ASC
注意:添加了 ORDER BY 语句的放置位置
Note: Added where to put ORDER BY statement
查询优化器也不应该多次计算平均值，所以这里不应该担心.
The query optimizer should also not calculate the Average multiple times either, so that should not be a concern here.
正如@jagra 的回答中提到的，您应该能够使用 AVG() 而不是 SUM()/COUNT()
As was mentioned in @jagra's answer, you should be able to use AVG() instead of SUM() / COUNT()
这篇关于SQL - SUM() 上的 WHERE 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！