chengben/Scripts/bigscreen/chengben_quzhun_2_city.sql

---2.有合同无局站
---chengben_quzhun_2_city_202203.csv
with vt as (select a.sid,a.sname,a.cname,a.cfname,a.cbzx,a.yuezujin,
b.city_name,b.city_code,b.district_code,b.hetong_sid,b.station_sid as station_sid_i,
c.station_sid as station_sid_s,
(case 
when b.hetong_sid is not null and c.station_sid is not null then '有合同有局站'--'合同系统有，资源系统没'
when b.hetong_sid is null or (b.hetong_sid is not null and c.station_sid is null) then '有合同无局站'--'合同系统有，资源系统没有' 
else '其他'
end) as check_result
from sqmdb_cost.hetong_new a
left join sqmdb_cost.station_hetong b on a.sid=b.hetong_sid
left join sqmdb_cost.station3 c on b.station_sid=c.station_sid)
select '202204' as sdate,t1.*,t2.city_code*100 as city_code from
(select substr(cname,5,length(cname)-7) as city,sid,sname,yuezujin as cost_hetong,cbzx
from vt where check_result='有合同无局站') t1
left join 
(select distinct city_code,city_sname from sqmdb_cost.dict_grid) t2 
on t1.city=t2.city_sname
where t2.city_code is not null
order by t2.city_code,t1.cost_hetong desc