WITH tmp1 as(
select nf,zc,ggbs,xmmc,sum(jsjyj_bq)/count(1) scj_bq
from "DM"."TM_YX_SCJGBB_Z_TH"
WHERE xmmc in ('江苏省','山东省' ,'安徽省' ,'上海市','浙江省',
'河南省','湖北省','湖南省','江西省','黑龙江省','吉林省','辽宁省','大连','北京市','天津市','河北省',
'内蒙古自治区','陕西省','山西省','甘肃省','宁夏回族自治区','青海省','新疆维吾尔自治区', '广东省','深圳',
'海南省','福建省','四川省','重庆市','贵州省','广西壮族自治区','云南省')
and ggbs in ('6901028112789','6901028111324','6901028114455','6901028062015',
'6901028062350','6901028115445')
group by nf,zc,ggbs,xmmc),
tmp2 as(
select nf,zc,a.ggbs,C.ggmc,xmmc,csmc,JSJYJ_BQ sfpjj,pfj--sum(jsjyj_bq)/count(1) scj_bq
from "DM"."TM_YX_SCJGBB_Z_TH" a
LEFT JOIN
(SELECT * FROM dm.TD_GGJQDZB WHERE ksrq>'2019-01-02') b
ON a.ggbs = b.ggdm
LEFT JOIN dm.td_gg c
ON a.ggbs=c.ggbs
WHERE csmc LIKE '%均价%'
and
a.ggbs in ('6901028112789','6901028111324','6901028114455','6901028062015',
'6901028062350','6901028115445')
ORDER BY
nf,zc,a.ggbs,xmmc,CSMC),
tmp3 as(
SELECT
a.nf ,a.zc,a.ggbs,b.ggmc,a.xmmc,b.csmc,pfj,b.sfpjj,a.scj_bq,CAST (ROUND(sfpjj/b.pfj,2) AS DECIMAL(12,2)) jzzs
FROM tmp1 a
LEFT JOIN tmp2 b
ON a.nf = b.nf AND a.zc = b.zc AND a.ggbs = b.ggbs AND a.xmmc = b.xmmc)
select sclx,a.xmmc,a.nf,a.zc,a.ggbs,B.ggmc,PFJ,SFPJJ,b.dxj,b.mbj,b.sxj,A.scj_bq,JZZS,
CAST (CASE WHEN A.scj_bq<dxj then 15
WHEN (A.scj_bq<dxj AND C.jzzs>1) THEN 20
when (dxj<=A.scj_bq and A.scj_bq<= mbj) THEN 1.00*(15+(A.scj_bq-dxj)*(10/(mbj-dxj)))
WHEN ((dxj<=A.scj_bq and A.scj_bq<= mbj) AND C.jzzs>1) THEN 25
when (mbj<=A.scj_bq and A.scj_bq<= sxj) then 30
WHEN A.scj_bq>dxj THEN 30
WHEN (A.scj_bq>dxj AND C.jzzs<1) THEN 25
ELSE 15
END AS DECIMAL(12,2)) JGFS
from tmp1 a
LEFT JOIN dm.test_jg_1 b
ON a.ggbs= b.ggbs AND a.xmmc=b.sfmc
LEFT JOIN tmp3 c
ON a.nf=c.nf AND a.zc=c.zc AND a.ggbs=c.ggbs and a.xmmc=c.xmmc
WHERE A.xmmc IN ('江苏省','甘肃省')
order BY sclx,nf,zc
