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));