MySQL CONCAT 多个字段与 IF 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14526886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL CONCAT multiple fields with IF statement
提问by dgig
I'm trying to do something I thought would be simple, but I'm stuck. I basically want to create a single address field from multiple address part fields, using an IF statement to use either an address or intersection. Here is my statement to make the field:
我正在尝试做一些我认为很简单的事情，但我被卡住了。我基本上想从多个地址部分字段创建一个地址字段，使用 IF 语句来使用地址或交集。这是我在该领域发表的声明：
CONCAT(loc_name,'\n',
IF ( add_number != '' && add_street != '' ) THEN
CONCAT(add_number,' ',add_street,'\n')
ELSEIF ( x_street_1 != '' && x_street_2 != '' ) THEN
CONCAT(x_street_1,' & ',x_street_2,'\n')
END IF
,city,', ',
IF ( state != '') THEN
CONCAT(state,' ',country,'\n')
ELSEIF ( x_street_1 != '' && x_street_2 != '' ) THEN
CONCAT(country,'\n')
END IF
) AS loc_info
But it doesn't like what I am doing at all, it throws an error at:
但它根本不喜欢我在做什么，它会在以下位置引发错误：
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN \n\t\t\t\t\t\tadd_number,' ',add_street,'\n'\n\t\t\t\t\tELSEIF ( x_street_1 != '' && x_"
Which seems like it doesn't like my empty field ('') notation. But I don't know why. Can I not use the IF statement inside a CONCAT like that?
这似乎不喜欢我的空字段 ('') 符号。但我不知道为什么。我不能在这样的 CONCAT 中使用 IF 语句吗？
Thanks for any insight.
感谢您的任何见解。
采纳答案by Kermit
The syntax is not correct. You want to use CASE:
语法不正确。你想使用CASE：
SET @loc_name = 'Location';
SET @add_street = 'Add Street';
SET @add_number = '10';
SET @x_street_1 = 'Street 1';
SET @x_street_2 = 'Street 2';
SET @city = 'City';
SET @state = 'State';
SET @country = 'Country';
SELECT Concat(@loc_name, '\n', CASE
WHEN @add_number != ''
AND @add_street != '' THEN
Concat(@add_number, ' ', @add_street, '\n')
WHEN @x_street_1 != ''
AND @x_street_2 != '' THEN
Concat(@x_street_1, ' & ', @x_street_2,
'\n')
end, @city, ', ', CASE
WHEN @state != '' THEN
Concat(@state, ' ', @country, '\n')
WHEN ( @x_street_1 != ''
AND @x_street_2 != '' ) THEN Concat(@country, '\n')
end) AS loc_info
Result
结果
| LOC_INFO | ----------------------------------------------- | Location 10 Add Street City, State Country |
Just find and replace @with .
只需找到并替换@为.
回答by Jason Swett
IIRC, the syntax you want to use is
IIRC，您要使用的语法是
IF(condition, expression_if_true, expression_if_false)
I could be wrong, but you might want to try that.
我可能是错的，但你可能想尝试一下。
回答by sourcecode
this might also help:
这也可能有帮助：
CONCAT(loc_name,'\n',
IF ( add_number != '' && add_street != '' ,
CONCAT(add_number,' ',add_street,'\n'),
IF ( x_street_1 != '' && x_street_2 != '' ,
CONCAT(x_street_1,' & ',x_street_2,'\n'),""
)
),
city,
',' ,
IF ( state != '',
CONCAT(state,' ',country,'\n'),
IF ( x_street_1 != '' && x_street_2 != '' ,
CONCAT(country,'\n'),""
)
) AS loc_info
also what are you comparing here state != ''is it against null values??
if so this will give you incorrect answeryou have to use state IS NOT NULLinstead of that.
还有你在这里比较的state != ''是什么与空值？
如果是这样，这会给您错误的答案，您必须使用它state IS NOT NULL来代替。
