如何将两个列连接成一个与现有的列名在 mysql？

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

例如: 我有一列 FIRSTNAME和 LASTNAME，还有很多列。我只想将这两列与 FIRSTNAME名称连接起来。

所以我试着这样做:

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

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

有没有办法使用 mysql 本身删除原始列？

681767 次浏览
小开

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.

小开
最佳答案

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;

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.

Hope that helps!

小开

Instead of getting all the table columns using * in your sql statement, you use to specify the table columns you need.

You can use the SQL statement something like: 

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

BTW, why couldn't you use FullName instead of FirstName? Like this: 

SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS 'CUSTOMER NAME' FROM customer;
小开

You can try this simple way for combining columns:

select some_other_column,first_name || ' ' || last_name AS First_name from customer;
小开

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
小开

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.


提交答案