别名和聚合函数的 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;

将 WHERE 替换为 HAVING 并产生此错误“您的 SQL 语法有错误；请查看与您的 MySQL 服务器版本对应的手册，了解在‘HAVING camera_id = ir_camera_id’附近使用的正确语法”。

在这一点上，我觉得我在黑暗中尝试使这个查询有效。有人会指导我朝着正确的方向发展，使其成为一个有效的查询吗？

谢谢!

Answer 1

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

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

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