chengben/Scripts/资源核查-陈学.sql

select * from
(select * from sqmdb_cost.station3 where station_sname like '%·ÅÖõã%') st
left join 
(select distinct station_sid from sqmdb_cost.equip e where profession='Òƶ¯ÎÞÏß') eq
on st.station_sid=eq.station_sid
where eq.station_sid is null


select station_sid,longitude,latitude from sqmdb_cost.station3 where station_sname like '%·ÅÖõã%'

--4G
select 
res.city_name,
res.station_sid,
res.siteid,
nctm.enbid as nctm4g
from
(select distinct 
city_name,
station_sid,
(regexp_split_to_array(r.cid, '\.'))[2] AS siteid
FROM (
SELECT dg.city_name,r.station_sid,unnest(string_to_array(r.cell_oid, '|')) AS cid 
FROM sqmdb_cost.station_rru r
left join (select distinct city_code,city_name from sqmdb_cost.dict_grid) dg
on r.city_code=dg.city_code
where r.station_sid is not null) r) res
left join 
(select distinct enbid from sqmdb_cost.cfg_0_4g_siteinfo) nctm
on res.siteid=nctm.enbid::varchar

--5G
select 
res.city_name,
res.station_sid,
res.siteid,
nctm.gnbid as nctm5g
from
(select distinct dg.city_name,station_sid,siteid 
from sqmdb_cost.station_aau a
left join (select distinct city_code,city_name from sqmdb_cost.dict_grid) dg
on a.city_code=dg.city_code
where a.station_sid is not null) res
left join 
(select distinct gnbid from sqmdb_cost.cfg_0_5g_siteinfo) nctm
on res.siteid=nctm.gnbid::varchar