chengben/Scripts/bigscreen/chengben_self_2_city.sql

--主要成本（万元）
--chengben_self_2_city_202203.csv
---地市
select 
jt.*,
sn.cost_total_sn,
sn.cost_fix_sn,
t2.city_code*100 as city_code 
from
(select 
a.smonth,
a.sname,
a.cost_total_jt,
a.cost_rental_tower_jt,
a.cost_nenghao_jt,
a.cost_rental_3rd_jt
from
(select '202204' as smonth,sname,stype,
sum(column1)/10000 as cost_total_jt,
sum(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115)/10000 as cost_nenghao_jt,
sum(column12+column15+column16)/10000 as cost_rental_3rd_jt,
sum(column2+column3)/10000 as cost_rental_tower_jt
from sqmdb_cost.report50 where period_type='M' and stype ='北方省汇总展现'
and smonth between '202201' and '202204' group by sname,stype) a
left join
(select report_id,smonth,sname,stype,column4 as income_year from sqmdb_cost.report80040) b
on a.smonth=b.smonth and a.sname=b.sname
where 
a.sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')) jt
left join
(select smonth,sname,cost_total as cost_total_sn,cost_fix as cost_fix_sn from sqmdb_cost.chengben_shengnei where stype='地市') sn
on jt.smonth=sn.smonth and jt.sname=sn.sname 
left join
(select distinct city_code,city_pro from sqmdb_cost.dict_grid) t2
on jt.sname=t2.city_pro
where jt.smonth='202204'
order by t2.city_code