MySQL SELECT AS 将两列合二为一
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18860233/
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 SELECT AS combine two columns into one
提问by Rocco The Taco
Using this solution, I tried to use COALESCE as part of a MySQL query that outputs to a csv file using SELECT As to name the column names when exporting the data.
使用此解决方案，我尝试将 COALESCE 用作 MySQL 查询的一部分，该查询使用 SELECT As 输出到 csv 文件以在导出数据时命名列名。
SELECT FirstName AS First_Name
, LastName AS Last_Name
, ContactPhoneAreaCode1
, ContactPhoneNumber1
, COALESCE(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1
I wanted 3 columns: First_Name, Last_Name and Contact_Phone
我想要 3 列：First_Name、Last_Name 和 Contact_Phone
I am getting 5 columns: First_Name, Last_Name, ContactPhoneAreaCode1, ContactPhoneNumber1 and Contact_Phone
我得到 5 列：First_Name、Last_Name、ContactPhoneAreaCode1、ContactPhoneNumber1 和 Contact_Phone
How do I hide the merging of ContactPhoneAreaCode1, ContactPhoneNumber1 into a single column for Contact_Phone from within the query?
如何在查询中将 ContactPhoneAreaCode1、ContactPhoneNumber1 的合并隐藏到 Contact_Phone 的单个列中？
回答by raina77ow
If both columns can contain NULL, but you still want to merge them to a single string, the easiest solution is to use CONCAT_WS():
如果两列都可以包含NULL，但您仍想将它们合并为一个字符串，最简单的解决方案是使用CONCAT_WS()：
SELECT FirstName AS First_Name
, LastName AS Last_Name
, CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1
This way you won't have to check for NULL-ness of each column separately.
这样您就不必分别检查NULL每列的 -ness。
Alternatively, if both columns are actually defined as NOT NULL, CONCAT()will be quite enough:
或者，如果两列实际上都定义为NOT NULL，则CONCAT()就足够了：
SELECT FirstName AS First_Name
, LastName AS Last_Name
, CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1
As for COALESCE, it's a bit different beast: given the list of arguments, it returns the first that's not NULL.
至于COALESCE，它有点不同：给定参数列表，它返回第一个不是NULL。
回答by sung
You don't need to list ContactPhoneAreaCode1 and ContactPhoneNumber1
您不需要列出 ContactPhoneAreaCode1 和 ContactPhoneNumber1
SELECT FirstName AS First_Name,
LastName AS Last_Name,
COALESCE(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1
回答by GamerJosh
You do not need to select the columns separately in order to use them in your CONCAT. Simply remove them, and your query will become:
您无需单独选择列即可在 CONCAT 中使用它们。只需删除它们，您的查询将变为：
SELECT FirstName AS First_Name
, LastName AS Last_Name
, CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1
回答by ganji
In case of NULLcolumns it is better to use IFclause like this which combine the two functions of : CONCATand COALESCEand uses special chars between the columns in result like space or '_'
在NULL列的情况下，最好使用这样的IF子句，它结合了 : CONCATand的两个功能，并COALESCE在结果的列之间使用特殊字符，如空格或“_”
SELECT FirstName , LastName ,
IF(FirstName IS NULL AND LastName IS NULL, NULL,' _ ',CONCAT(COALESCE(FirstName ,''), COALESCE(LastName ,'')))
AS Contact_Phone FROM TABLE1
