MySQL两列或多列求和的问题及coalesce函数
1、问题
(文章开头的资源为本次使用的SQL脚本文件)在对数据库的两列或多列进行求和时,其中如果有一列为如果存在值为null的行,那么求和后在对应行上求和字段的值也为null,下面两个SQL查询语句展示的就是这种情况:
mysql> select * from my_test;
+----+------+-------+
| id | age | money |
+----+------+-------+
| 1 | 12 | NULL |
| 2 | 11 | NULL |
| 3 | NULL | 1 |
+----+------+-------+
mysql> select id, age, money, age + money as temp from my_test;
+----+------+-------+-------------+
| id | age | money | temp |
+----+------+-------+-------------+
| 1 | 12 | NULL | NULL |
| 2 | 11 | NULL | NULL |
| 3 | NULL | 1 | NULL |
+----+------+-------+-------------+
2、解决方案
我们希望12加上null得到12,而不是null,为此需要将null转换为0值,在MySQL中可以使用IFNULL()函数,该函数也是MySQL独有的,它接收两个参数,语法如下:
IFNULL(expression, alt_value)
这两个参数都是必传的,其中expression是被用来判断值是否为null的表达式,若为null则返回第二个参数alt_value的值,若不为null则返回expression的值。
在上面求和的SQL中使用ifnull()函数,再次做查询操作:
mysql> select id, age, money, ifnull(age, 0) + ifnull(money, 0) as temp from my_test;
+----+------+-------+------+
| id | age | money | temp |
+----+------+-------+------+
| 1 | 12 | NULL | 12 |
| 2 | 11 | NULL | 11 |
| 3 | NULL | 1 | 1 |
+----+------+-------+------+
可以看到结果如我们所期望的。当做其他算术运算时如果有null值也可以考试使用ifnull()函数。
3、扩展一:sum()函数的使用
sum()函数可以返回某一列的数据总和,当该列有值为null的记录时,并不会导致整个求和结果为null,下面为对money列使用sum()后的结果:
mysql> select sum(money) from my_test;
+------------+
| sum(money) |
+------------+
| 1 |
+------------+
4、扩展二:coalesce()函数的使用
COALESCE函数与IFNULL函数
在SQL内置的函数中,有一个函数也具备与IFNULL()函数类似的功能,它便是COALESCE()函数。
既然是SQL内置的函数,它就能用在所有实现了SQL标准的数据库产品中,它需要一个或多个参数(当只传两个参数时,它所实现的效果同IFNULL()函数),返回第一个non-null参数,如果所有的参数都是null则返回Null,如下面的执行结果:
mysql> select coalesce(null, null, '1');
+---------------------------+
| coalesce(null, null, '1') |
+---------------------------+
| 1 |
+---------------------------+