SELECT `name`, AVG(`name`) FROM ceshi
GROUP BY `name`
跑一下这条sql你就知道了。这里的AVG(name)并不是所有name的平均值,而是group by后的name的平均值,所以name
解决方法:
1、在程序里先跑一边统计平均值的sql,然后把计算好的统计值填入该sql;
2、(不推荐在生产环境使用)having里面使用子查询,
SELECT `name` FROM ceshi
GROUP BY `name`
HAVING `name` < (SELECT AVG(`name`) FROM ceshi);
3、使用变量(本质上就是1),
SET @name_avg = (SELECT AVG(`name`) FROM ceshi);
SELECT `name` FROM ceshi
GROUP BY `name`
HAVING `name` < @name_avg;