怎么在mysql里添加前缀_Mysql在Select语句中为列名添加前缀

4 个答案:

答案 0 :(得分：1)

使用concat函数，逗号(，)是一种旧方法，尝试使用join：

SELECT

a.OnlineStatus,

b.CustomerName,

b.CustomerContact

FROM customer_db a

JOIN customer_channel b

ON a.CustomerID = concat('private-customer-', b.ChannelName)

答案 1 :(得分：0)

在WHERE语句中为列名添加'文本前缀'

不，除非您将其作为动态查询(预备语句)，否则不能这样做。仅允许列或表名称别名使用前缀或后缀。

(或)您可以使用子查询并使用该子查询结果执行JOIN

Select a.OnlineStatus,

b.CustomerName,

b.CustomerContact

FROM customer_db a JOIN ( select CustomerName, CustomerContact,

'private-customer-'+ ChannelName as channelName

from customer_channel ) b

ON a.CustomerID = b.channelName;

答案 2 :(得分：0)

尝试使用Concat函数..

Select a.OnlineStatus,

b.CustomerName,

b.CustomerContact

FROM customer_db a,

customer_channel b

WHERE a.CustomerID = concat('private-customer-', b.ChannelName)

答案 3 :(得分：0)

你可以使用replace或substring来做到这一点

例如给出

drop table if exists a;

create table a(CustomerID int, CustomerName varchar(5), CustomerContact int);

insert into a values

(1, 'John' , 1234),

(2, 'Anna' , 5678),

(3, 'Angel', 2468);

drop table if exists b;

create table b (ChannelName varchar(20), OnlineStatus varchar(3));

insert into b values

('private-customer-1' , 'YES'),

('private-customer-2' , 'NO');

Select b.OnlineStatus, a.CustomerName, a.CustomerContact

FROM a

join b on a.CustomerID = replace(b.ChannelName,'private-customer-','');

Select b.OnlineStatus, a.CustomerName, a.CustomerContact

FROM a

join b on a.CustomerID = substring(b.ChannelName,18,length(b.channelname) -17)

两个查询都会导致

+--------------+--------------+-----------------+

| OnlineStatus | CustomerName | CustomerContact |

+--------------+--------------+-----------------+

| YES | John | 1234 |

| NO | Anna | 5678 |

+--------------+--------------+-----------------+