如何在MySQL中计算空值？
要在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)