coalesce函数_coalesce函数 - 温家三哥

具体怎么用呢？

上面的函数说明通俗来说coalesce()的作用是：返回传入的参数中第一个非null的值。expre1不为空值则返回expre1；否则判断expre2是否是空值，如果expre2不为空值则返回expre2；否则判断expre3是否是空值，如果expre3不为空值则返回expre3；……以此类推，如果所有的表达式都为空值，则返回NULL。 

为简单起见，举例如下：

  1、    input：select coalesce(NULL,NULL,1) 

output：1

2、    如果传入的参数所有都是NULL，则返回NULL，比如 
input：select coalesce(NULL,NULL,NULL,NULL,NULL)
output：NULL

3、    最常用的一种用法是假设某个字段除了有实例的记录之外都是默认值NULL，但后续计算需要用到这个字段，如果为NULL的话无法计算或展示，现在想将该字段中的NULL值全部替换成0，则可以使用coalesce()函数。以下的sql能够很好的展示其具体是怎么进行操作的：

CREATE TABLE
IF NOT EXISTS test (
id INT (11) PRIMARY KEY auto_increment COMMENT '主键id',
score INT (11) COMMENT '分数',
score_avg INT (11) COMMENT '平均分数'
);

INSERT INTO test(id, score,score_avg)
VALUES
(1, 90, 80),
(2, 56, 80),
(3, NULL, 80),
(4, 69, 80),
(5, 89, 80),
(6, 99, 80),
(7, 100, 80),
(8, NULL, 80),
(9, 47, 80),
(10, NULL, 80),
(11, NULL, 80),
(12, 78, 80);

以上是建表语句以及插入数据的Mysql语句。现在要将score中的NULL值替换成0。

select id,score,coalesce(score, 0) as score_act from test;

从以上结果清楚的看出，coalesce(score, 0) as score_act新的一列当score不是NULL值时取原值，当score是NULL值时取0。

4、在例子3的基础上再举一个例子。

update test set score_avg = NULL where id = 3;

则原始数据如下所示：

 select id, score, score_avg, coalesce(score, score_avg, 0) as score_act from test;
结果如下：

可以看出，coalesce(score,score_avg,0) as score_act表示：

当score = 非空值时，score_act = score 例如id = 1、4

当score = NULL值时，看score_avg的值，若score_avg = 非空值，score_act = score_avg 例如id = 8、10、11

                                                                   若score_avg = NULL,则score_act =0 例如id = 3

转载于：