sql 2017-10-27

SELECT     t3.xxid,     t3.xxmc,     t3.xxdf,     t3.xjdf,     t3.sjdf,      t4.zjdf,    ROUND((t3.sjdf * 0.2) + (t4.zjdf * 0.8), 2) AS zhdf,    t4.tjsl FROM(     SELECT    t1.xx_id AS xxid,    t1.xxmc AS xxmc,    SUM(TO_NUMBER(CASE WHEN t1.xxzpdf IS NULL THEN '0' WHEN TRIM(t1.xxzpdf) = '' THEN '0' ELSE t1.xxzpdf END)) AS xxdf,    SUM(TO_NUMBER(CASE WHEN t1.xsqhcdf IS NULL THEN '0' WHEN TRIM(t1.xsqhcdf) = '' THEN '0' ELSE t1.xsqhcdf END)) AS xjdf,    SUM(TO_NUMBER(CASE WHEN t1.sqspgdf IS NULL THEN '0' WHEN TRIM(t1.sqspgdf) = '' THEN '0' ELSE t1.sqspgdf END)) AS sjdf    FROM T_YJREP_JTGLYQ t1 GROUP BY t1.xx_id, t1.xxmc) t3, (    SELECT    t2.xx_id AS xxid,    SUM(TO_NUMBER(CASE WHEN t2.pfhz IS NULL THEN '0' WHEN TRIM(t2.pfhz) = '' THEN '0' ELSE t2.pfhz END)) AS zjdf,    SUM(TO_NUMBER(CASE WHEN t2.is_tj IS NULL THEN '0' WHEN TRIM(t2.is_tj) = '' THEN '0' ELSE t2.is_tj END)) AS tjsl    FROM T_YJ_ZJRW t2 GROUP BY t2.xx_id) t4 WHERE     t3.xxid = t4.xxid --AND t3.xxid = '1154'ORDER BY t3.xxid;