我有2个表,分别称为dataTest和custlist。
我想基于客户清单表中的“ vlookref”加入这些表,
但是在dataTest表中唯一匹配的列是'custacc'。
在dataTest表中,可以按区域对区域进行分类
例如“中部,东部,北部,南部”为半岛,
“哥打基纳巴卢语,拉哈达图,桑达坎语,斗湖”为沙巴语,以及
其他人像砂拉越。
如何归档此文件,以便我可以基于客户列表中的'vlookref'进行内部连接。
dateTest模式
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');
最佳答案
以下查询可能会帮助您:
从中选择临时区域,临时custacc,客户清单1. *
(
选择不同的dataTest.region,
dataTest.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)
否则concat('sarawak','',dataTest.custacc)结束)vlookrefdata
来自custlist1,dataTest
)temp JOIN custlist1 ON temp.vlookrefdata = custlist1.vlookref;
关于mysql - MYSQL设置用于连接表的临时列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52176470/