计算MySQL表中非空或非零列的数量？
使用if()方法。让我们首先创建一个表-
mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Number1 int, -> Number2 int -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(Number1,Number2) values(10,20); mysql> insert into DemoTable(Number1,Number2) values(0,32); mysql> insert into DemoTable(Number1,Number2) values(40,0); mysql> insert into DemoTable(Number1,Number2) values(40,50);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
输出结果
这将产生以下输出-
+----+---------+---------+ | Id | Number1 | Number2 | +----+---------+---------+ | 1 | 10 | 20 | | 2 | 0 | 32 | | 3 | 40 | 0 | | 4 | 40 | 50 | +----+---------+---------+ 4 rows in set (0.00 sec)
以下是查询以计算表中非空或非零列的数量的查询-
mysql> select *, -> if(Number1 <> 0,1,0)+if(Number2 <> 0,1,0) AS TotalCount -> from DemoTable;
输出结果
这将产生以下输出-
+----+---------+---------+------------+ | Id | Number1 | Number2 | TotalCount | +----+---------+---------+------------+ | 1 | 10 | 20 | 2 | | 2 | 0 | 32 | 1 | | 3 | 40 | 0 | 1 | | 4 | 40 | 50 | 2 | +----+---------+---------+------------+ 4 rows in set (0.00 sec)