我必须从数据库中提取客户列表,但是如果第二个地址字段为空,我想显示文本“ None Provided”。这是我的查询:
select concat(first_name, " ", last_name) as CustomerName, address, address2, postal_code from customer inner join address using (address_id);
如果地址2为空白,我想显示“无提供”。我怎么做?我敢肯定答案非常简单,但是我脑子屁,在任何地方都找不到答案。
好的,这就是我最终要进行的工作:
case when address2=" " or address2 is null then 'None Provided' else address2 end as address2,
有没有更好的方法来实现这一目标?
您可以使用CASE:
CASE
select concat(first_name, " ", last_name) as CustomerName, address, case when address2 is null then 'None Provided' else address2 end as address2, postal_code from customer inner join address using (address_id);
甚至更好COALESCE:
COALESCE
select concat(first_name, " ", last_name) as CustomerName, address, COALESCE(address2,'None Provided') as address2, postal_code from customer inner join address using (address_id);
如果数据包含空字符串或null,那么我将考虑使用:
select concat(first_name, " ", last_name) as CustomerName, address, case when address2 is null or address2 = '' then 'None Provided' else address2 end as address2, postal_code from customer inner join address using (address_id);