select f.CONTRACT_CODE 资源编号,
p2.deptname 片区,
f.DUTY_DEPTNAME 责任店面,
f.duty_username 责任人,
f.input_date 录入时间,
f.house_tel 资源电话,
u.tel 责任人电话,
u.yun_tel 责任人云总机,
f.if_public 备案OR公盘
from mm_fyinfo f
join(select a.tel tel
from oy_user a
where a.ifdeleted =0
and a.tel isnotnull
and a.username <>'系统管理员'
union
select b.yun_tel tel
from oy_user b
where b.ifdeleted =0
and b.yun_tel isnotnull
and b.username <>'系统管理员') v
on f.status ='有效'
--and f.input_date > date '2009-01-01'
and f.ifdeleted =0
and f.If_Tzcd =0
and f.Consign_Type <>'限时代理'
--and f.duty_deptname like '%谈固%'
andinstr(f.house_tel, v.tel)>0
join oy_user u
on u.user_id = f.duty_userid
join oy_department p
on f.DUTY_DEPTID = p.deptid
join oy_department p2
on p.pid = p2.deptid