chengben/Scripts/kpi31/17_city.sql

--17、户均装机料费
---地市
select 
fz.smonth as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
fm.city_code*100 as CITY_ID,
fz.sname as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'17' as ZB_CODE,
'户均装机料费' as ZB_NAME,
'否' as IS_RATIO,
fz.cost_liao as QZ_FZ,
fm.order_num as QZ_FM,
fz.cost_liao/fm.order_num/6 as BY_QZ
from
(select smonth,sname,cost_liao from sqmdb_cost.chengben_shengnei cs
where stype='地市' and smonth='202206') fz
join
(select b.city_code,b.city_pro,a.order_num from
(select smonth,city_name,sum(order_num)/10000 as order_num 
from sqmdb_cost.zhuangji z where smonth='202206'
group by smonth,city_name) a
join
(select distinct city_code,city_name,city_pro from sqmdb_cost.dict_grid dg) b
on a.city_name=b.city_name) fm
on fz.sname=fm.city_pro
order by fm.city_code