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 ('江蘇省','山東省' ,'安徽省' ,'上海市','浙江省',
'河南省','湖北省','湖南省','江西省','黑龍江省','吉林省','遼寧省','大連','北京市','天津市','河北省',
'內(nèi)蒙古自治區(qū)','陜西省','山西省','甘肅省','寧夏回族自治區(qū)','青海省','新疆維吾爾自治區(qū)', '廣東省','深圳',
'海南省','福建省','四川省','重慶市','貴州省','廣西壯族自治區(qū)','云南省')
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,pfj,ZTLSJ_BQ,JSJYJ_BQ sfpjj--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,ztlsj_bq,b.sfpjj,a.scj_bq,CAST (ROUND(sfpjj/b.pfj,2) AS DECIMAL(12,2)) jzzs,CAST(ROUND(ztlsj_bq/pfj,2) AS DECIMAL(12,2)) hlzs
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,c.csmc,SFPJJ,ztlsj_bq,b.dxj,b.mbj,b.sxj,A.scj_bq,JZZS,hlzs,
CAST (CASE
WHEN? (A.scj_bq<dxj AND C.jzzs>1) THEN 20
WHEN A.scj_bq<dxj? then 15
WHEN ((dxj<=A.scj_bq and A.scj_bq<= mbj) AND C.jzzs>1) THEN 25
when (dxj<=A.scj_bq and A.scj_bq<= mbj) THEN 1.00*(15+(A.scj_bq-dxj)*(10/(mbj-dxj)))
? ? when (mbj<=A.scj_bq and A.scj_bq<= sxj) then 30
? ? WHEN (A.scj_bq>sxj AND C.jzzs<1) THEN 30
? ? WHEN A.scj_bq>dxj? THEN 25
? ? ELSE 15?
? ? END AS DECIMAL(12,2)) JGFS,
CASE WHEN jzzs<1.0 THEN 20
WHEN jzzs<=1.2 AND jzzs>=1.0 THEN 20+(jzzs-1.0)*5
? ? ELSE 0
? ? END jzfs,
CASE WHEN hlzs<1.0 THEN hlzs*15
WHEN hlzs>1.0 THEN 15
ELSE 0
END hlfs? ?
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
