MySQL 如何使用mysql中现有的列名将两列合并为一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20284528/
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
how to concat two columns into one with the existing column name in mysql?
提问by Manik
I want to concatenate two columns in a table with a existing column name using mysql.
我想使用 mysql 将表中的两列与现有列名连接起来。
An example: I am having a column FIRSTNAMEand LASTNAMEand so many columns also. I want to concatenate these two columns with the name of FIRSTNAMEonly.
一个例子：我有一个专栏FIRSTNAME，LASTNAME还有很多专栏。我想将这两列与FIRSTNAMEonly的名称连接起来。
So I tried like this:
所以我试过这样：
SELECT *, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;
but it displaying the two fields with the name of FIRSTNAME. one field is having normal values and another one is having concatenated values. I want only one column with those concatenate value. I can select single columns, but am having more than 40 columns in my table.
但它显示名称为 的两个字段FIRSTNAME。一个字段具有正常值，另一个字段具有连接值。我只想要一列带有这些连接值的列。我可以选择单列，但我的表中有 40 多列。
Is there any way to remove the original column using mysql itself?
有没有办法使用mysql本身删除原始列？
回答by Raad
As aziz-shaikh has pointed out, there is no way to suppress an individual column from the *directive, however you might be able to use the following hack:
正如 aziz-shaikh 指出的那样，无法从*指令中抑制单个列，但是您可以使用以下 hack：
SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
c.*
FROM `customer` c;
Doing this will cause the second occurrence of the FIRSTNAMEcolumn to adopt the alias FIRSTNAME_1so you should be able to safely address your customised FIRSTNAMEcolumn. You need to alias the table because *in any position other than at the start will fail if not aliased.
这样做将导致第二次出现的FIRSTNAME列采用别名，FIRSTNAME_1因此您应该能够安全地处理您的自定义FIRSTNAME列。您需要对表进行别名，因为*如果没有别名，那么在开始以外的任何位置都将失败。
Hope that helps!
希望有帮助！
回答by Alex Alvarez
Instead of getting all the table columns using * in your sql statement, you use to specify the table columns you need.
不是在 sql 语句中使用 * 获取所有表列，而是用于指定所需的表列。
You can use the SQL statement something like:
您可以使用以下 SQL 语句：
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS FIRSTNAME FROM customer;
BTW, why couldn't you use FullName instead of FirstName? Like this:
顺便说一句，你为什么不能使用 FullName 而不是 FirstName？像这样：
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS 'CUSTOMER NAME' FROM customer;
回答by Ayush gupta
You can try this simple way for combining columns:
您可以尝试这种简单的方法来组合列：
select some_other_column,first_name || ' ' || last_name AS First_name from customer;
回答by Aziz Shaikh
Remove the *from your query and use individual column names, like this:
*从您的查询中删除并使用单个列名，如下所示：
SELECT SOME_OTHER_COLUMN, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;
Using *means, in your results you want all the columns of the table. In your case *will also include FIRSTNAME. You are then concatenating some columns and using alias of FIRSTNAME. This creates 2 columns with same name.
使用*手段，在你的结果中你想要表格的所有列。在您的情况下，*还将包括FIRSTNAME. 然后您将连接一些列并使用FIRSTNAME. 这将创建 2 个具有相同名称的列。
回答by Babasaheb SP
Just Remove * from your select clause, and mention all column names explicitly and omit the FIRSTNAME column. After this write CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME. The above query will give you the only one FIRSTNAME column.
只需从您的 select 子句中删除 *，并明确提及所有列名并省略 FIRSTNAME 列。在此之后写入 CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME。上述查询将为您提供唯一的 FIRSTNAME 列。
回答by user10689173
I am a novice and I did it this way:
我是新手，我是这样做的：
Create table Name1
(
F_Name varchar(20),
L_Name varchar(20),
Age INTEGER
)
Insert into Name1
Values
('Tom', 'Bombadil', 32),
('Danny', 'Fartman', 43),
('Stephine', 'Belchlord', 33),
('Corry', 'Smallpants', 95)
Go
Update Name1
Set F_Name = CONCAT(F_Name, ' ', L_Name)
Go
Alter Table Name1
Drop column L_Name
Go
Update Table_Name
Set F_Name
