使用MySQL where子句并按avg()排序以查找重复的单个元素的平均值
为此，请使用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) < 9.5 -> order by avg(Score);
这将产生以下输出-
+-------+------------+ | Name | avg(Score) | +-------+------------+ | David | 6.0000 | | Bob | 6.5000 | | Chris | 7.0000 | +-------+------------+ 3 rows in set (0.00 sec)