如何计算MySQL数据库上的水平值？
您可以使用COUNT()MySQL的聚合函数来计算数据库中的水平值。让我们首先创建一个表-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstValue int, SecondValue int, ThirdValue int, FourthValue int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(FirstValue,SecondValue,ThirdValue,FourthValue) values(-18,45,0,155); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(FirstValue,SecondValue,ThirdValue,FourthValue) values(0,235,null,15); Query OK, 1 row affected (0.20 sec)
以下是使用select语句显示表中所有记录的查询-
mysql> select *from DemoTable;
这将产生以下输出-
+----+------------+-------------+------------+-------------+ | Id | FirstValue | SecondValue | ThirdValue | FourthValue | +----+------------+-------------+------------+-------------+ | 1 | -18 | 45 | 0 | 155 | | 2 | 0 | 235 | NULL | 15 | +----+------------+-------------+------------+-------------+ 2 rows in set (0.00 sec)
这是查询数据库中水平值的查询-
mysql> SELECT COUNT(FirstValue > 0 OR NULL) + COUNT(SecondValue > 0 OR NULL) + COUNT(ThirdValue > 0 OR NULL) + COUNT(FourthValue > 0 OR NULL) AS TOTAL_VALUE FROM DemoTable;
这将产生以下输出-
+-------------+ | TOTAL_VALUE | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)