mysql coalesce函数_mysql coalesce函数

COALESCE函数从值列表中返回第一个非NULL的值，当遇到NULL值时将其替换为0。 coalesce(str1,str2....);

e.g.   需要在表中查出所有比'WARD'提成(COMM)低的员工，提成为NULL的员工也包括在内。 (个人意见，如果数据库提成字段默认值不是为0值的话肯定是开发那个的错)。

select ename,comm from emp where coalesce(comm,0) < (select comm from emp whrer ename ="WARD");

结果：

+--------+------+

| ename | comm |

+--------+------+

| SMITH | NULL |

| ALLEN | 300 |

| JONES | NULL |

| BLAKE | NULL |

| CLARK | NULL |

| SCOTT | NULL |

| KING | NULL |

| TURNER | 0 |

| JAMES | NULL |

| MILLER | NULL |

| ADAMS | NULL |

| FORD | NULL |

+--------+------+

12 rows in set

返回非NULL值：

select ename, comm,coalesce(comm,0) from emp where coalesce(comm,0) < (select comm from emp where ename = 'WARD');

+--------+------+------------------+

| ename | comm | coalesce(comm,0) |

+--------+------+------------------+

| SMITH | NULL | 0 |

| ALLEN | 300 | 300 |

| JONES | NULL | 0 |

| BLAKE | NULL | 0 |

| CLARK | NULL | 0 |

| SCOTT | NULL | 0 |

| KING | NULL | 0 |

| TURNER | 0 | 0 |

| JAMES | NULL | 0 |

| MILLER | NULL | 0 |

| ADAMS | NULL | 0 |

| FORD | NULL | 0 |

+--------+------+------------------+

12 rows in set