如何在单个 MySQL 查询中使用 COUNT() 和 IF()?
让我们首先创建一个表 -
mysql> create table DemoTable ( isValidUser boolean ); Query OK, 0 rows affected (0.70 sec)
使用 insert 命令在表中插入一些记录 -
mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.09 sec)
使用 select 语句从表中显示所有记录 -
mysql> select *from DemoTable;
这将产生以下输出 -
+-------------+ | isValidUser | +-------------+ | 1 | | 0 | | 0 | | 1 | | 1 | | 1 | | 0 | +-------------+ 7 rows in set (0.00 sec)
现在让我们看看在单个 MySQL 查询中使用 COUNT() 和 IF() 的查询 -
mysql> select count(if(isValidUser=1,1,NULL)) AS TOTAL_NUMBER_OF_ONE from DemoTable;
这将产生以下输出 -
+---------------------+ | TOTAL_NUMBER_OF_ONE | +---------------------+ | 4 | +---------------------+ 1 row in set (0.06 sec)
广告