chengben/Scripts/bigscreen/chengben_quzhun_3_city.sql

---3.有能耗无局站
---chengben_quzhun_3_city_202203.csv
with vt as (select nh.smonth,nh.city_name,nh.build_code,nh.build_name,nh.accrued_cost,nh.cost_unit_name,s.station_sid,
(case when s.station_sid is not null then '有能耗有局站' else '有能耗无局站' end) as check_result
from
(select smonth,city_name,area_name,build_code_short,build_code,build_name,cost_unit_name,accrued_cost from sqmdb_cost.ytmx_new) nh
left join
(select distinct station_sid,nh_sid_short from sqmdb_cost.station_nenghao) sy
on nh.build_code_short=sy.nh_sid_short
left join
(select station_sid,station_sname from sqmdb_cost.station3) s
on sy.station_sid=s.station_sid
where nh.smonth='202204')
select t1.*,t2.city_code*100 as city_code from 
(select smonth,city_name,build_code,build_name,accrued_cost,cost_unit_name from vt where check_result='有能耗无局站') t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2 
on t1.city_name=t2.city_name
order by t2.city_code,t1.cost_unit_name