根据两个已有表建立新表

create table clue.dust as 
SELECT distinct c.company_name,c.reg_no, c.city, c.bus_scope, case when b.flag=1 then 1 else 0 end as new_flag
FROM  
(select company_name, reg_no,city,bus_scope,province from clue.clue where province='北京' and length(bus_scope)>=6 and length(reg_no)>=15)
c
LEFT JOIN   
(select DISTINCT full_name, flag FROM   clue_pre.bj_sj where flag=1) b
ON c.company_name = b.full_name



create table clue.dust as 
SELECT distinct c.company_name,c.reg_no, c.city, c.bus_scope, b.flag
FROM  clue.clue  c
LEFT JOIN  clue_pre.bj_sj  b
ON c.company_name = b.full_name
where b.flag=1 and province='北京' and length(bus_scope)>=6 and length(reg_no)>=15
select count(1) from clue.clue where province='北京' and length(bus_scope)>=6 and length(reg_no)>=15  ##764066

根据两个已有表建立新表