MySQL SELECT条件CONCAT [英] MySQL SELECT Conditional CONCAT

问题描述

我有一个基本上保存地址的数据库

I have a database which basically holds addresses

表(tblAddress)看起来像这样...

The table (tblAddress) looks like this...

housename    | housenumber | address1    | address2        | address3 | town      | postcode
Banana House |          29 | Acacia Road | Yellow Skin Way |          | Nuttytown | W1 1MP

当我根据邮政编码搜索数据库时，我希望能够返回并得到如下结果...

When I search the database based on a postcode I want to be able to return and result like this...

Banana House,29 Acacia Road,Yellow Skin Way,Nuttytown,W1 1MP

因此，如果填充了地址1，那么我需要与地址1串联的门牌号码.如果不是，则与地址2或地址3连接.然后按照示例跟随地址的其余部分.

So I need the housenumber concatenated with address1 IF address1 is populated. If not then concat with address2 or address3. Then the rest of the address to follow as per the example.

我尝试使用IF和CASE语句，但是似乎无法到达我想要的输出附近.

I tried using IF and CASE statements but can't seem to get anywhere near the output I'm after.

希望如此.

推荐答案

您可以通过添加一些concat操作来做到这一点.

You can do it by adding few concat operations.

检查下面的代码，它应该可以工作.

Check below code it should work.

SELECT CONCAT(housename, CONCAT(" ",CONCAT(housenumber, CONCAT(" ",CONCAT_WS(' ,', 
                  NULLIF(address1, ''), 
                  NULLIF(address2, ''), 
                  NULLIF(address3, ''),
                  NULLIF(town, ''),
                  NULLIF(postcode, '')))))) AS concatedAddress FROM tblAddress;

这篇关于MySQL SELECT条件CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！