WITH tmp AS (select nf,zc,a.ggbs,b.ggmc,xmmc,sum(jsjyj_bq)/count(1) scj_bq

from "DM"."TM_YX_SCJGBB_Z_TH" a left join dm.td_gg b on a.ggbs = b.ggbs

where xmmc in ('江蘇省','山東省' ,'安徽省' ,'上海市','浙江省',

'河南省','湖北省','湖南省','江西省','黑龍江省','吉林省','遼寧省','大連','北京市','天津市','河北省',

'內(nèi)蒙古自治區(qū)','陜西省','山西省','甘肅省','寧夏回族自治區(qū)','青海省','新疆維吾爾自治區(qū)', '廣東省','深圳',

'海南省','福建省','四川省','重慶市','貴州省','廣西壯族自治區(qū)','云南省') and a.ggbs in ('6901028112789','6901028111324','6901028114455','6901028062015',

'6901028062350','6901028115445')

group by nf,zc,a.ggbs,b.ggmc,xmmc)

select sclx,xmmc,nf,zc,a.ggbs,a.ggmc,b.dxj,b.mbj,b.sxj,scj_bq,

CASE WHEN scj_bq<dxj then 15

when (dxj<=scj_bq and scj_bq<= mbj) then 1.00*(15+(scj_bq-dxj)*(10/(mbj-dxj)))

? ? when (mbj<=scj_bq and scj_bq<= sxj) then 30

? ? WHEN scj_bq>dxj then 25

? ? ELSE 15? END FS

from tmp a LEFT JOIN dm.test_jg_1 b ON a.ggbs= b.ggbs AND a.xmmc=b.sfmc

WHERE xmmc='江蘇省'

order BY nf,zc