数据结构
网络
RDBMS
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化学
生物
数学
英语
经济学
心理学
社会研究
时尚研究
法律研究在单个 MySQL 查询中计算布尔字段值？
要在一个查询中计算布尔字段值，可以使用 CASE 语句。让我们为示例创建一个演示表 -
mysql> create table countBooleanFieldDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentFirstName varchar(20), -> isPassed tinyint(1) -> ); Query OK, 0 rows affected (0.63 sec)
使用 insert 命令在表中插入一些记录。
查询如下 -
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Larry',0);
Query OK, 1 row affected (0.12 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Mike',1);
Query OK, 1 row affected (0.17 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Sam',0);
Query OK, 1 row affected (0.21 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Carol',1);
Query OK, 1 row affected (0.15 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Bob',1);
Query OK, 1 row affected (0.16 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('David',1);
Query OK, 1 row affected (0.13 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Ramit',0);
Query OK, 1 row affected (0.28 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Chris',1);
Query OK, 1 row affected (0.20 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Robert',1);
Query OK, 1 row affected (0.16 sec)使用 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)
更新时间： 30-Jul-2019
1 千次+
广告