create table A
(
id varchar(3),
地址1 varchar(30),
地址2 varchar(30),
地址3 varchar(30)
)

create table b
(
  id varchar(10),
  ComAddress varchar(30),
  ComCity varchar(30)
 
)

insert into A select '002', 'guangzhou', 'chengdu', 'nanjing'

insert into b 
select 'A001', 'guang', 'nan' union all
select 'A002', 'hubei', 'dong'  union all
select 'A003', 'chengdu', 'xinjian'  union all
select 'A004', 'xiaoxin', 'linken'  


select t1.* from b t1,A t2 where CHARINDEX(t1.ComAddress ,t2.地址1 + t2.地址2 + t2.地址3 ) >0 and CHARINDEX(t1.ComCity  ,t2.地址1 + t2.地址2 + t2.地址3 ) >0


id         ComAddress                     ComCity
---------- ------------------------------ ------------------------------
A001       guang                          nan

(1 行受影响)