MySQL ERROR 1111 (HY000): 组函数的使用无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28171977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR 1111 (HY000): Invalid use of group function
提问by Achyuta Aich
given the following table staff (ec,name,code,dob,salary)
给定下表工作人员（ec,name,code,dob,salary）
Q. List the staff members earning more than the average salary
Q. 列出收入高于平均工资的工作人员
My soln. select* from staff where salary > avg(salary);
我的溶液。 select* from staff where salary > avg(salary);
What is wrong in it?
它有什么问题？
回答by Raging Bull
An aggregate may not appear in the WHEREclause unless it is in a subquery contained in a HAVINGclause or a select list, and the column being aggregated is an outer reference.
聚合可能不会出现在WHERE子句中，除非它位于HAVING子句或选择列表中包含的子查询中，并且被聚合的列是外部引用。
Example using WHEREclause :
使用WHERE子句示例：
select *
from staff
where salary > (select avg(salary) from staff)
Example using HAVINGclause :
使用HAVING子句示例：
select deptid,COUNT(*) as TotalCount
from staff
group by deptid
having count(*) > 2
Havingclause specifies a search condition for a group or an aggregate. HAVINGcan be used only with the SELECTstatement. HAVINGis typically used in a GROUP BYclause. When GROUP BYis not used, HAVINGbehaves like a WHEREclause.
Having子句指定组或聚合的搜索条件。HAVING只能与SELECT语句一起使用。HAVING通常用在GROUP BY子句中。当GROUP BY不使用时，HAVING就像一个WHERE条款。
