使用 MySQL AVG 函数时，我们可以排除带有"0"的条目吗?
mysqlmysqli database
要排除带有"0"的条目，您需要将 NULLIF() 与函数 AVG() 一起使用。
语法如下
SELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;
Let us first create a table
mysql> create table AverageDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > StudentName varchar(20), - > StudentMarks int - > ); Query OK, 0 rows affected (0.72 sec)
使用 insert 命令在表中插入一些记录。
查询结果如下
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Adam',NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Larry',23);
Query OK, 1 row affected (0.19 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Mike',0);
Query OK, 1 row affected (0.20 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Sam',45);
Query OK, 1 row affected (0.18 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Bob',0);
Query OK, 1 row affected (0.12 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('David',32);
Query OK, 1 row affected (0.18 sec)
使用 select 语句显示表中的所有记录。
查询结果如下
mysql> select *from AverageDemo;
下面是输出结果
+----+-------------+--------------+ | Id | StudentName | StudentMarks | +----+-------------+--------------+ | 1 | Adam | NULL | | 2 | Larry | 23 | | 3 | Mike | 0 | | 4 | Sam | 45 | | 5 | Bob | 0 | | 6 | David | 32 | +----+-------------+--------------+ 6 rows in set (0.00 sec)
以下是使用 AVG 时排除带有"0"的条目的查询
mysql> select AVG(nullif(StudentMarks, 0)) AS Exclude0Avg from AverageDemo;
下面是输出结果
+-------------+ | Exclude0Avg | +-------------+ | 33.3333 | +-------------+ 1 row in set (0.05 sec)
