在单个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)
热门推荐
4 修祖屋祝福语简短
10 团队文案祝福语简短励志
11 到同事新家简短祝福语
12 写给男生简短的祝福语
13 姐姐女儿结婚祝福语简短
14 送外卖祝福语大全简短
15 祝贺开业文案祝福语简短
16 九子订婚祝福语简短
17 祝寿时祝福语简短精辟
18 幼儿狗年祝福语大全简短