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