mysql为连接表设置临时列

vsikbqxv  于 2021-06-20  发布在  Mysql
关注(0)|答案(3)|浏览(364)

我有两个表叫做datatest和custlist。我想基于custlist表中的“vlookref”联接这些表,但唯一匹配的列是datatest表中的“custacc”。根据数据测试表,该地区可按地区分类,例如“中部、东部、北部、南部”为半岛,“kota kinabalu、lahad datu、sandakan、tawau”为沙巴,其他为沙捞越。如何将其归档,以便我可以基于custlist tables.thx中的“vlookref”对其进行内部联接
日期测试架构

CREATE TABLE dataTest ( region varchar(50),custacc varchar(50));

INSERT INTO dataTest VALUES ('central','CT0135');
INSERT INTO dataTest VALUES ('eastern','CT0135');
INSERT INTO dataTest VALUES ('southern','CT0135');
INSERT INTO dataTest VALUES ('northern','CT0135');
INSERT INTO dataTest VALUES ('kota kinabalu','CT0135');
INSERT INTO dataTest VALUES ('lahad datu','CT0135');
INSERT INTO dataTest VALUES ('sandakan','CT0135');
INSERT INTO dataTest VALUES ('tawau','CT0135');
INSERT INTO dataTest VALUES ('bintulu','CT0135');
INSERT INTO dataTest VALUES ('kuching','CT0135');
INSERT INTO dataTest VALUES ('sibu','CT0135');

客户列表架构

CREATE TABLE custlist1 ( area varchar(50),vlookref varchar(50),custacc varchar(50),custname varchar(50));

INSERT INTO custlist1 VALUES ('peninsular','peninsular CT0135','CT0135','HP sdn bhd');
INSERT INTO custlist1 VALUES ('sabah','sabah CT0135','CT0135','Hup Trading sdn bhd');
INSERT INTO custlist1 VALUES ('sarawak','sarawak CT0135','CT0135','Master sdn bhd');
nfeuvbwi

nfeuvbwi1#

可以使用子字符串索引使用内部连接条件,
试试这样的

SELECT ct.*, dt.* 
FROM custlist1 ct INNER JOIN dataTest dt 
ON SUBSTRING_INDEX(ct.vlookref,' ',-1) = dt.custacc

SELECT ct.*, dt.* 
FROM custlist1 ct INNER JOIN dataTest dt 
ON ct.custacc = dt.custacc;

试试这个演示

s5a0g9ez

s5a0g9ez2#

以下查询可能对您有所帮助:
从中选择temp.region、temp.custacc、custlist1.*(
选择distinct datatest.region,
数据测试.custacc,
(当(datatest.region='central'或datatest.region='eastern'或datatest.region='northern'或datatest.region='southern')然后是concat('peninsular','',datatest.custacc)时的情况)
当(datatest.region='kota kinabalu'或datatest.region='lahad datu'或datatest.region='sandakan'或datatest.region='tawau')然后concat('sabah','',datatest.custacc)
else concat('sarawak','',datatest.custacc)end)来自custlist1的vlookrefdata,datatest
)temp.vlookrefdata=custlist1.vlookref上的temp join custlist1;

z0qdvdin

z0qdvdin3#

从您的表中可以看出,它似乎来自excel工作表,在excel工作表中,执行查找(vlookup)的唯一方法是将两列连接在一起。sql中不是这样的,您只需像这样连接:

from datatest
join custlist1 on custlist1.area=datatest.region 
     and custlist1.custacc=datatest.custacc

相关问题