123456789101112131415161718 |
- ---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
|