WITH tmp AS (
SELECT a.nfdm,a.djz,ggbs,ggmc,csbs,csmc,SYXL_BQZLJ FROM
(SELECT rqdm,nfdm,djz FROM dm.TD_RQ WHERE zj=7 AND rqdm>'2017-12-31') a
LEFT JOIN DM.TM_YX_SYGXCRB_GGCS b ON a.rqdm=b.rq
),
tmp2 AS (
SELECT * FROM (
SELECT a.nfdm,a.djz,a.ggbs,a.ggmc,a.csbs,a.csmc,a.SYXL_BQZLJ,jsjyj_bq,ztlsj_bq,b.jgqjbs,b.pfj,ksrq FROM tmp a
LEFT JOIN (SELECT ggdm,jgqjbs,pfj,ksrq FROM dm.TD_GGJQDZB WHERE jsrq='3000-12-31') b ON a.ggbs=b.ggdm
LEFT JOIN "DM"."TM_YX_SCJGBB_Z_TH" c ON a.nfdm=c.nf AND a.djz=c.zc AND a.ggbs = c.ggbs AND a.csbs = c.csbs
)WHERE jsjyj_bq IS NOT NULL)
SELECT nfdm,djz zc,jgqjbs,sum(syxl_bqzlj*jsjyj_bq)/sum(syxl_bqzlj) jqpjjg FROM tmp2
GROUP BY nfdm,djz,jgqjbs
ORDER BY nfdm,djz,jgqjbs
