MySQL 查询计算哪些列的值超过三列为真?
mysqlmysqli database
要计算哪些列的值超过三列为真，可以使用 WHERE 子句。让我们首先创建一个表 −
mysql> create table DemoTable -> ( -> isMarried boolean, -> isActive boolean, -> isMember boolean, -> isOn boolean -> ); Query OK, 0 rows affected (0.61 sec)
使用 insert 命令在表中插入一些记录 −
mysql> insert into DemoTable values(true,false,true,false); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(false,false,false,false); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(true,true,true,true); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(true,false,false,true); Query OK, 1 row affected (0.55 sec)
使用 select 语句显示表中的所有记录 −
mysql> select *from DemoTable;
输出
+-----------+----------+-----------+------+ | isMarried | isActive | isMember | isOn | +-----------+----------+-----------+------+ | 1 | 0 | 1 | 0 | | 0 | 0 | 0 | 0 | | 1 | 1 | 1 | 1 | | 1 | 0 | 0 | 1 | +-----------+----------+-----------+------+ 4 rows in set (0.00 sec)
以下查询用于计算三个以上列值为真的情况 −
mysql> select *from DemoTable where isMarried+isActive+isMember+isOn >=3;
输出
+-----------+----------+-----------+------+ | isMarried | isActive | isMember | isOn | +-----------+----------+-----------+------+ | 1 | 1 | 1 | 1 | +-----------+----------+-----------+------+ 1 row in set (0.00 sec)
