智能聚结

Question

我有一个有一些基本地址数据的表：

​

​

如果街道和邮政编码/城镇都填满了，我要用逗号分隔街道和邮政编码/城镇。

select Concat(coalesce(Street,''),", ", coalesce(Postcode,'')," ",coalesce(Town,'')) from adresses

如果没有街道，我希望只有邮政编码和城镇之间的空白，如果连邮编也没有，我只想要名称(如果只是邮政编码丢失，我想要“街，镇”)

我如何设计查询，以便它考虑什么是可用的，哪里需要放置逗号和空白？

Answer 1

使用内置concat_ws()最简单的方法

select concat_ws(', ', Street, Postcode, Town)
from adresses;

如果其中任何参数是NULL (除分隔符外)，则concat_ws()只需跳过该字符串和相关分隔符。

Answer 2

select
    (case when
        street is not null
        and postcode is not null
        and town is not null
    then concat(street,', ',postcode,', ',town)
    when 
        street is null
        and postcode is not null
        and town is not null
    then concat(postcode,' ',town)
    when
        street is null
        and postcode is null
        and town is not null
    then town
    else 'N/A'
    end) myAddress
from address

根据您的数据，您可能必须检查空字符串，即

when street is not null and street <> ''

Answer 3

从“LCI_DW_views.UACI_TRMTRULEINV”中选择“街道”，“‘，’，‘’，从'%644970%‘这样的位置选择’