SQL - WHERE条件对SUM（）(SQL - WHERE Condition on SUM

是否有可能做这样的事情：

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条件上的划分结果值

Answer 1:

这可以用HAVING子句来完成：

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语句

查询优化器也不能平均多次计算要么，所以这不应该是一个问题在这里。

正如@ jagra的答案被提到的，你应该能够使用AVG()而不是SUM() / COUNT()

Answer 2:

您可以使用HAVING子句：

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 
GROUP BY `vt`.`review_id` 
HAVING rating >= 0;

当你的问题被标记的MySQL这个解决方案应该工作，如文档所示：

这个扩展默认情况下，在MySQL官方允许编译。

参考： http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html （这个词第一次出现HAVING在页面上）

Answer 3:

您需要使用聚合函数过滤时HAVING子句：

SELECT 
  # `e`.*, => is this needed
  `rt`.`review_id`, 
  (SUM(vt.percent) / COUNT(vt.percent)) AS rating 
  AVG(vt.percent) AS rating1 # same as above 
FROM `catalog_product_entity` AS `e` 
INNER JOIN `rating_option_vote` AS `vt`
  ON vt.review_id = e.review_id 
GROUP BY `vt`.`review_id`
HAVING AVG(vt.percent) > 0

另外两个注意事项：

1）SUM（X）/ COUNT（x）的<=> AVG（x）的

2）你是包括电子。*在选择，但不是由组。 MySQL的让你做，但其他数据库的习惯。

Answer 4:

使用HAVING骨料条件（比如你有一个）

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'
HAVING (SUM(vt.percent) / COUNT(vt.percent)) > 0

文章来源: SQL - WHERE Condition on SUM()