您可以使用以下语法对单个行的值求和-
情况1-如果您的栏没有NULL值,则语法如下-SELECT yourColumnName1+yourColumnName2+yourColumnName3+.......+N as anyVariableName FROM yourTableName;
情况2-如果您的列具有NULL值,则使用此语法-SELECT IFNULL(yourColumnName1,0)+ IFNULL(yourColumnName2,0)+ IFNULL(yourColumnName3,0)+.............+N
AS anyVariableName FROM yourTableName;
为了理解上述语法,让我们创建一个表。创建表的查询如下-mysql> create table SumValueOfSingleRow
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> FirstValue int,
-> SecondValue int,
-> ThirdValue int,
-> PRIMARY KEY(Id)
-> );
使用insert命令在表中插入一些记录。查询如下-mysql> insert into SumValueOfSingleRow(FirstValue,SecondValue,ThirdValue) values(1,0,1);
mysql> insert into SumValueOfSingleRow(FirstValue,SecondValue,ThirdValue) values(1,NULL,0);
mysql> insert into SumValueOfSingleRow(FirstValue,SecondValue,ThirdValue) values(NULL,NULL,NULL);
使用select语句显示表中的所有记录。查询如下-mysql> select *from SumValueOfSingleRow;
以下是输出-+----+------------+-------------+------------+
| Id | FirstValue | SecondValue | ThirdValue |
+----+------------+-------------+------------+
| 1 | 1 | 0 | 1 |
| 2 | 1 | NULL | 0 |
| 3 | NULL | NULL | NULL |
+----+------------+-------------+------------+
3 rows in set (0.00 sec)
查看示例输出。如果使用情况1,则不会获得适当的结果。
让我们检查一下上面讨论的情况1。查询如下-mysql> select FirstValue+SecondValue+ThirdValue as SingleRowSum from SumValueOfSingleRow;
以下是输出-+--------------+
| SingleRowSum |
+--------------+
| 2 |
| NULL |
| NULL |
+--------------+
3 rows in set (0.06 sec)
查看示例输出,以上查询不处理NULL情况。现在,您可以使用CASE 2获得适当的结果。
查询如下-mysql> select ifnull(FirstValue,0)+ ifnull(SecondValue,0)+ ifnull(ThirdValue,0) as SingleRowSum from SumValueOfSingleRow;
以下是显示单行值总和的输出-+--------------+
| SingleRowSum |
+--------------+
| 2 |
| 1 |
| 0 |
+--------------+
3 rows in set (0.06 sec)