如何在mysql中使用现有列名将两列合并成一列？ [英] how to concat two columns into one with the existing column name in mysql?

问题描述

我想使用mysql将表中的两个列与现有的列名连接。

I want to concatenate two columns in a table with a existing column name using mysql.

例如：我有一个列 FIRSTNAME 和 LASTNAME 这么多列也。我想把这两列连接到 FIRSTNAME 。

An example: I am having a column FIRSTNAME and LASTNAME and so many columns also. I want to concatenate these two columns with the name of FIRSTNAME only.

所以我试过这样： p>

So I tried like this:

 SELECT *, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;

但显示两个字段名为 FIRSTNAME 。一个字段具有正常值，另一个字段具有连接值。我只想要一个具有这些连接值的列。我可以选择单个列，但在我的表中有超过40列。

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.

有什么办法使用mysql本身删除原始列吗？

Is there any way to remove the original column using mysql itself?

推荐答案

正如aziz-shaikh指出的，没有办法从 * 指令，但你可以使用下面的hack：

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:

SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
       c.*
FROM   `customer` c;

这样做会导致第二次出现 FIRSTNAME 列采用别名 FIRSTNAME_1 ，因此您应该能够安全地处理您的自定义 FIRSTNAME 列。您需要对表进行别名，因为 * 在开头之外的任何位置都会失败，如果没有别名。

Doing this will cause the second occurrence of the FIRSTNAME column to adopt the alias FIRSTNAME_1 so you should be able to safely address your customised FIRSTNAME column. You need to alias the table because * in any position other than at the start will fail if not aliased.

希望有所帮助！

这篇关于如何在mysql中使用现有列名将两列合并成一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！