or 左联 子查询 （接入网线路连接情况）

select * from res_spec rs where rs.name like ('%ODF%');
201502 OBD
201201 ODF
201202 光交

--OBD总数
select (select aa.name from area aa where aa.area_id = de.local_net_id),
       count(de.device_id)
  from device de
 where de.res_spec_id = 201502
   and de.status = 'U'
 group by rollup(de.local_net_id);

431  长春
432  吉林
433  延边
434  四平
435  通化
436  白城
437  辽源
438  松原
439  白山

select * from area aa where aa.area_id in ('431','432','433','434','435','436','437','438','439');


--OLT_PON口总数
select count(pp.connector_id)
  from connector pp
 where pp.maint_sts = 'N'
   and pp.device_id in (select de.device_id
                          from device de
                         where de.status = 'U'
                           and de.local_net_id = 439
                           and de.res_spec_id = 201502)
 group by pp.local_net_id;
 
 select count(cc.connector_id),cc.local_net_id
   from connector cc
   left join device de
     on de.device_id = cc.device_id
  where de.status = 'U'
    and cc.maint_sts = 'N'
    and de.res_spec_id = 201502
  group by rollup(cc.local_net_id) ;
  
  select count(cc.connector_id), cc.local_net_id
    from connector cc, device de
   where de.device_id = cc.device_id(+)
     and de.status = 'U'
     and cc.maint_sts = 'N'
     and de.res_spec_id = 201502
   group by rollup(cc.local_net_id);
   
--OLT_PON口关联ODF端子数 
select count(pp.physical_conn_id),pp.local_net_id
  from physical_conn pp
 where pp.maint_sts = 'N'
   and ((PP.Z_PARENT_RES_SPEC_ID = 201201 and
       pp.a_res_id in (select cc.connector_id
                           from connector cc, device de
                          where de.device_id = cc.device_id(+)
                            and de.status = 'U'
                            and cc.maint_sts = 'N'
                            and de.res_spec_id = 201502)) or
       (PP.a_PARENT_RES_SPEC_ID = 201201 and
       pp.z_res_id in (select cc.connector_id
                           from connector cc, device de
                          where de.device_id = cc.device_id(+)
                            and de.status = 'U'
                            and cc.maint_sts = 'N'
                            and de.res_spec_id = 201502)))group by rollup (pp.local_net_id);
                            
                            
--OBD关联光交设备数                         
select count(pp.physical_conn_id), pp.local_net_id
  from physical_conn pp
 where pp.maint_sts = 'N'
   and ((pp.a_parent_res_id in
       (select de.device_id
            from device de
           where de.status = 'U'
             and de.res_spec_id = 201502) and
       pp.z_parent_res_spec_id = 201202) or
       ((pp.z_parent_res_id in
       (select de.device_id
             from device de
            where de.status = 'U'
              and de.res_spec_id = 201502) and
       pp.a_parent_res_spec_id = 201202)))
 group by rollup (pp.local_net_id) ;

-----------------丹姐的求pon口数量以及关联数量----------------

select dd.local_net_id, count(distinct pp.name)
  from device dd, port pp
 where dd.status = 'U'
   and dd.res_spec_id = '201501'
   and dd.device_id = pp.device_id
   and pp.maint_sts = 'N'
   and dd.name not like '%测试%'
 group by rollup((dd.local_net_id));


select dd.local_net_id, count(distinct pp.name)
  from device dd, port pp
 where dd.status = 'U'
   and dd.res_spec_id = '201501'
   and dd.device_id = pp.device_id
   and pp.maint_sts = 'N'
   and dd.name not like '%测试%'
   and exists (select 1
          from physical_conn ph
         where ph.maint_sts = 'N'
           and ph.res_spec_id = '402102'
           and ph.local_net_id = dd.local_net_id
           and (ph.a_parent_res_id = dd.device_id and
               ph.a_res_id = pp.port_id and
               ph.z_parent_res_spec_id = '201201' or
               ph.z_parent_res_id = dd.device_id and
               ph.z_res_id = pp.port_id and
               ph.a_parent_res_spec_id = '201201'))
 group by rollup((dd.local_net_id));