在单个MySQL查询中计算布尔字段值？
要在单个查询中计算布尔字段值，可以使用CASE语句。让我们为示例创建一个演示表-
mysql> create table countBooleanFieldDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentFirstName varchar(20), -> isPassed tinyint(1) -> );
使用insert命令在表中插入一些记录。
查询如下-
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Larry',0);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Mike',1);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Sam',0);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Carol',1);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Bob',1);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('David',1);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Ramit',0);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Chris',1);
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Robert',1);使用select语句显示表中的所有记录。
查询如下-
mysql> select *from countBooleanFieldDemo;
这是输出-
+-----------+------------------+----------+ | StudentId | StudentFirstName | isPassed | +-----------+------------------+----------+ | 1 | Larry | 0 | | 2 | Mike | 1 | | 3 | Sam | 0 | | 4 | Carol | 1 | | 5 | Bob | 1 | | 6 | David | 1 | | 7 | Ramit | 0 | | 8 | Chris | 1 | | 9 | Robert | 1 | +-----------+------------------+----------+ 9 rows in set (0.00 sec)
这是在单个查询中对布尔字段值进行计数的查询-
mysql> select sum(isPassed= 1) as `True`, sum(isPassed = 0) as `False`, -> ( -> case when sum(isPassed = 1) > 0 then sum(isPassed = 0) / sum(isPassed = 1) -> end -> )As TotalPercentage -> from countBooleanFieldDemo;
以下是输出-
+------+-------+-----------------+ | True | False | TotalPercentage | +------+-------+-----------------+ | 6 | 3 | 0.5000 | +------+-------+-----------------+ 1 row in set (0.00 sec)