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 |
+--------------+--------------+-----------------+