MySQL SELECT AS将两列合并为一 [英] MySQL SELECT AS combine two columns into one

问题描述

使用此解决方案，我尝试将COALESCE用作使用SELECT As输出到csv文件的MySQL查询，以在导出数据时命名列名.

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.

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , ContactPhoneAreaCode1
     , ContactPhoneNumber1
     , COALESCE(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

我想要3列:名字，姓氏和联系电话

I wanted 3 columns: First_Name, Last_Name and Contact_Phone

我得到5列:名字，姓氏，ContactPhoneAreaCode1，ContactPhoneNumber1和Contact_Phone

I am getting 5 columns: First_Name, Last_Name, ContactPhoneAreaCode1, ContactPhoneNumber1 and Contact_Phone

如何在查询中将ContactPhoneAreaCode1和ContactPhoneNumber1的合并隐藏到Contact_Phone的单个列中?

How do I hide the merging of ContactPhoneAreaCode1, ContactPhoneNumber1 into a single column for Contact_Phone from within the query?

推荐答案

如果两列都可以包含NULL，但是您仍然希望将它们合并为单个字符串，则最简单的解决方案是使用

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():

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

通过这种方式，您不必分别检查每列的NULL -ness.

This way you won't have to check for NULL-ness of each column separately.

或者，如果两列都实际定义为NOT NULL，则

Alternatively, if both columns are actually defined as NOT NULL, CONCAT() will be quite enough:

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

对于COALESCE来说，它有点不同:在给出参数列表的情况下，它返回不是NULL的第一个参数.

As for COALESCE, it's a bit different beast: given the list of arguments, it returns the first that's not NULL.

这篇关于MySQL SELECT AS将两列合并为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！