为此,请使用having子句而不是where。让我们首先创建一个表-mysql> create table DemoTable1338
-> (
-> Name varchar(10),
-> Score int
-> );
使用insert命令在表中插入一些记录。在这里,我们插入了带分数的重复名称-mysql> insert into DemoTable1338 values('Chris',8);
mysql> insert into DemoTable1338 values('Bob',4);
mysql> insert into DemoTable1338 values('Bob',9);
mysql> insert into DemoTable1338 values('Chris',6);
mysql> insert into DemoTable1338 values('David',5);
mysql> insert into DemoTable1338 values('David',7);
使用select语句显示表中的所有记录-mysql> select * from DemoTable1338;
这将产生以下输出-+-------+-------+
| Name | Score |
+-------+-------+
| Chris | 8 |
| Bob | 4 |
| Bob | 9 |
| Chris | 6 |
| David | 5 |
| David | 7 |
+-------+-------+
6 rows in set (0.00 sec)
以下是查询以查找重复的单个元素的平均值-mysql> select Name,avg(Score) from DemoTable1338
-> group by Name
-> having avg(Score)
-> order by avg(Score);
这将产生以下输出-+-------+------------+
| Name | avg(Score) |
+-------+------------+
| David | 6.0000 |
| Bob | 6.5000 |
| Chris | 7.0000 |
+-------+------------+
3 rows in set (0.00 sec)