mysql 无效使用组函数_解决错误1111(HY000)：在MySQL中无效使用组函数？如何正确使用带有where子句的聚合函数？...

为了在MySQL中的where子句中正确使用聚合函数，以下是语法-select *from yourTableName

where yourColumnName > (select AVG(yourColumnName) from yourTableName);

为了理解上述概念，让我们创建一个表。创建表的查询如下-mysql> create table EmployeeInformation

-> (

-> EmployeeId int,

-> EmployeeName varchar(20),

-> EmployeeSalary int,

-> EmployeeDateOfBirth datetime

-> );

现在，您可以使用insert命令在表中插入一些记录。查询如下-mysql> insert into EmployeeInformation values(101,'John',5510,'1995-01-21');

mysql> insert into EmployeeInformation values(102,'Carol',5600,'1992-03-25');

mysql> insert into EmployeeInformation values(103,'Mike',5680,'1991-12-25');

mysql> insert into EmployeeInformation values(104,'David',6000,'1991-12-25');

mysql> insert into EmployeeInformation values(105,'Bob',7500,'1993-11-26');

使用select语句显示表中的所有记录。查询如下-mysql> select *from EmployeeInformation;

以下是输出-+------------+--------------+----------------+---------------------+

| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |

+------------+--------------+----------------+---------------------+

|        101 | John         |           5510 | 1995-01-21 00:00:00 |

|        102 | Carol        |           5600 | 1992-03-25 00:00:00 |

|        103 | Mike         |           5680 | 1991-12-25 00:00:00 |

|        104 | David        |           6000 | 1991-12-25 00:00:00 |

|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |

+------------+--------------+----------------+---------------------+

5 rows in set (0.00 sec)

这是将聚合与where子句一起使用的正确方法。查询如下-mysql> select *from EmployeeInformation

-> where EmployeeSalary > (select AVG(EmployeeSalary) from EmployeeInformation);

以下是输出-+------------+--------------+----------------+---------------------+

| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |

+------------+--------------+----------------+---------------------+

|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |

+------------+--------------+----------------+---------------------+

1 row in set (0.04 sec)