要在MySQL中计算空值,可以使用CASE语句。我们首先来看一个示例并创建一个表-mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName varchar(20)
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable(FirstName) values('John');
mysql> insert into DemoTable(FirstName) values(null);
mysql> insert into DemoTable(FirstName) values('');
mysql> insert into DemoTable(FirstName) values('Larry');
mysql> insert into DemoTable(FirstName) values('');
mysql> insert into DemoTable(FirstName) values(null);
mysql> insert into DemoTable(FirstName) values(null);
mysql> insert into DemoTable(FirstName) values('Bob');
以下是使用select语句显示表中所有记录的查询-mysql> select *from DemoTable;
这将产生以下输出-+----+-----------+
| Id | FirstName |
+----+-----------+
| 1 | John |
| 2 | NULL |
| 3 | |
| 4 | Larry |
| 5 | |
| 6 | NULL |
| 7 | NULL |
| 8 | Bob |
+----+-----------+
8 rows in set (0.00 sec)
这是对MySQL中的Null值进行计数的查询-mysql> select sum(case when FirstName IS NULL then 1 else 0 end) as NUMBER_OF_NULL_VALUE from DemoTable;
这将产生以下输出-+----------------------+
| NUMBER_OF_NULL_VALUE |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)