解决 MySQL 中的错误 1111 (HY000):在组函数中使用无效?如何正确与 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
   -> );
Query OK, 0 rows affected (1.08 sec)

现在,可以使用 insert 命令在表格中插入一些记录。查询如下 −

mysql> insert into EmployeeInformation values(101,'John',5510,'1995-01-21');
Query OK, 1 row affected (0.13 sec)
mysql> insert into EmployeeInformation values(102,'Carol',5600,'1992-03-25');
Query OK, 1 row affected (0.56 sec)
mysql> insert into EmployeeInformation values(103,'Mike',5680,'1991-12-25');
Query OK, 1 row affected (0.14 sec)
mysql> insert into EmployeeInformation values(104,'David',6000,'1991-12-25');
Query OK, 1 row affected (0.23 sec)
mysql> insert into EmployeeInformation values(105,'Bob',7500,'1993-11-26');
Query OK, 1 row affected (0.16 sec)

使用 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)

更新于: 30-07-2019

2K+ 浏览量

启动你的职业

通过完成课程取得认证

开始使用
广告