MySQL中COALESCE()和IFNULL()的区别-CSDN博客
1,查找没有shipper_id的记录,并把标题改为shipper
use sql_store;
select
order_id,
ifnull(shipper_id,'not assigned') as shipper
from orders
2,用coalesce函数,如果shipper_id是空的,返回comments那列的值,如果comments也是空的,就显示not assigned
use sql_store;
select
order_id,
coalesce(shipper_id,comments,'not assigned') as shipper
from orders
对比原表,没有问题
3,在2的基础上,加上一列 ifnull(shipper_id,'...'),shipper_id null 的时候显示...
use sql_store;
select
order_id,
ifnull(shipper_id,'...'),
coalesce(shipper_id,comments,'not assigned') as shipper
from orders
4,练习:写一段查询
答案:这里用ifnull也是可以的
use sql_store;
select
concat(first_name, ' ',last_name)as customer,
coalesce(phone,'unknown') as phone
from customers