join 的记录

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