sql关联表查询结果并插入

时间:2021-11-28 05:46:08

这里涉及三个表,AA,BB,CC,将AA的数据更新到CC表中,将AA中LABEL_ID分别截取字段与BB表中的label_id对应查询到LABEL_NAME作为CC表的一个字段插入,这里分成四段查询

insert into CC

select T.apmac,T1.LABEL_NAME,T2.LABEL_NAME,T3.LABEL_NAME,T4.LABEL_NAME,T.LABEL_ID,'杭州'

from(select a.AP_MAC as apmac,a.LABEL_ID ,substr(a.label_id,3,2)as level1,substr(a.label_id,5,2)as level2,

substr(a.label_id,7,3)as level3,substr(a.label_id,10,3)as level4 from AA a) T,

(select * from BB where label_level='一级')T1,

(select * from BB where label_level='二级')T2,

(select * from BB where label_level='三级')T3,

(select * from BB where label_level='四级')T4

WHERE T1.LABEL_ID=T.level1 and T2.LABEL_ID=T.level2 and T3.LABEL_ID=T.level3 and T4.LABEL_ID=T.level4