MySQL 别名和聚合函数的查询问题

Question

我有一个麻烦的 MySQL 查询，如下所示：

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
WHERE camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND avg_price < 150 
AND camera_id != 1411
AND camera_id != 9
ORDER BY rand();

这个查询产生了“'where 子句'中的未知列'avg_price'”错误。我理解这是因为 WHERE 子句中不允许使用列别名。 （如果我对此有任何错误，请纠正我。）

因此，我像这样调整了查询​​：

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
WHERE camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND ((avg(low_price) + avg(high_price)) / 2) < 150 
AND camera_id != 1411
AND camera_id != 9
ORDER BY rand();

用实际计算替换别名，并且此查询产生错误：“无效使用组函数”。我理解这是因为 avg() 在 WHERE 子句完成处理之后才会发生。

然后我尝试：

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
ORDER BY rand();
HAVING camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND avg_price < 150 
AND camera_id != 1411
AND camera_id != 9;

用 HAVING 替换 WHERE 并产生此错误“您的 SQL 语法中有错误；检查与您的 MySQL 服务器版本对应的手册，以获取在 'HAVINGcamera_id = ir_camera_id' 附近使用的正确语法” 。

在这一点上，我觉得我是在黑暗中尝试让这个查询起作用。有人会引导我朝正确的方向使其成为一个有效的查询吗？

谢谢！

Answer 1

1. 尽管您可以使用 WHERE 指定连接条件，但最好在 LEFT[INNER] JOIN 子句中执行。
2. 如果要按非聚合字段过滤，请将过滤器放入WHERE，如果需要按聚合过滤，请将条件移动到HAVING

3. 当使用聚合和同一查询中的非聚合，不要忘记GROUP BY。

   选择camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
来自camera_general
内部连接camera_products ON（camera_id = ir_camera_id）
哪里 dp_post_dt 不为空
并且 dp_post_dt 不像“0000%”
AND current_manufactured = '是'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
并且camera_id！= 1411
并且camera_id！= 9
按camera_id分组
平均价格 < 150
ORDER BY rand();

Answer 2

ORDER 子句应位于 HAVING 子句之后。 （此外，您在 ORDER BY rand() 之后放置一个分号 ;，然后继续使用 HAVING，这实际上是另一个查询的开始，因为首先以 ; 结束）。