错误1111(HY000):无效使用组功能 [英] ERROR 1111 (HY000): Invalid use of group function

问题描述

给出下表 人员(ec，姓名，代码，职位，工资)

问列出收入高于平均工资的员工

我的儿子. select* from staff where salary > avg(salary);

里面有什么问题?

解决方案

除非在HAVING子句或选择列表中包含的子查询中，否则聚合可能不会出现在WHERE子句中.聚集是外部参考.

使用WHERE子句的示例:

select *
from staff 
where salary > (select avg(salary) from staff)

使用HAVING子句的示例:

select deptid,COUNT(*) as TotalCount
from staff
group by deptid
having count(*) > 2

Having子句为组或集合指定搜索条件. HAVING只能与SELECT语句一起使用. HAVING通常在GROUP BY子句中使用.不使用GROUP BY时，HAVING的行为类似于WHERE子句.

given the following table staff (ec,name,code,dob,salary)

Q. List the staff members earning more than the average salary

My soln. select* from staff where salary > avg(salary);

What is wrong in it?

解决方案

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Example using WHERE clause :

select *
from staff 
where salary > (select avg(salary) from staff)

Example using HAVING clause :

select deptid,COUNT(*) as TotalCount
from staff
group by deptid
having count(*) > 2

Having clause specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

这篇关于错误1111(HY000):无效使用组功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！