chengben/Scripts/合同校验_正向.sql

--数据校验 合同系统 <- 合同索引 <- 局站表
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.hetong2 a
left join sqmdb_cost.station_hetong b on a.sid=b.hetong_sid||E'\t'
left join sqmdb_cost.station2 c on b.station_sid=c.station_sid)
select t1.*,t2.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
order by t2.city_code,t1.cost_hetong desc