chengben/Scripts/bigscreen/chengben_quzhun_4_city.sql

---4.有铁塔订单无局站
---chengben_quzhun_4_city_202203.csv
with vt as (select t.sdate,t.city_operator,t.site_addr_id,dtc.tower_name,t.total_cost,dtc.cbzx,tf.fzd_id,
(case when tf.fzd_id is not null then '有铁塔订单有局站' else '有铁塔订单无局站' end) as check_result
from
(select sdate,city_operator,site_addr_id,sum(total_cost) as total_cost from sqmdb_cost.tower3 
group by sdate,city_operator,site_addr_id) t
left join sqmdb_cost.dict_tower_cbzx dtc 
on t.site_addr_id=dtc.tower_code 
left join sqmdb_cost.tower_fzd tf 
on t.city_operator=tf.city_name and t.site_addr_id||E'\t'=tf.tower_code
where t.sdate='202204')
select t1.*,t2.city_code*100 as city_code from 
(select sdate,city_operator,site_addr_id,tower_name,total_cost,cbzx from vt where check_result='有铁塔订单无局站') t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2 
on t1.city_operator=t2.city_name
order by t2.city_code,t1.tower_name,t1.total_cost desc