select
(CASE WHEN SUM( CASE wzlb WHEN '结构类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '结构类' THEN 1 END) END) a,
(CASE WHEN SUM(CASE wzlb WHEN '周转材料类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '周转材料类' THEN 1 END) END) b,
(CASE WHEN SUM(CASE wzlb WHEN '水电类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '水电类' THEN 1 END) END) c,
(CASE WHEN SUM(CASE wzlb WHEN '机械类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '机械类' THEN 1 END) END) d ,
(CASE WHEN SUM(CASE wzlb WHEN '临设土建类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '临设土建类' THEN 1 END) END) e ,
(CASE WHEN SUM(CASE wzlb WHEN '其他类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '其他类' THEN 1 END) END) f,
to_char((CASE WHEN SUM( CASE wzlb WHEN '结构类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '结构类' THEN 1 END) END)/
(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1) END)*100,'FM990.00') AS SSLV,
to_char( (CASE WHEN SUM(CASE wzlb WHEN '周转材料类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '周转材料类' THEN 1 END) END)/
(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1) END)*100,'FM990.00') AS WSSLV,
to_char((CASE WHEN SUM(CASE wzlb WHEN '水电类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '水电类' THEN 1 END) END)/
(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1) END)*100,'FM990.00') AS CYLV,
to_char((CASE WHEN SUM(CASE wzlb WHEN '机械类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '机械类' THEN 1 END) END)/
(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1) END)*100,'FM990.00') AS SSL1V,
to_char( (CASE WHEN SUM(CASE wzlb WHEN '临设土建类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '临设土建类' THEN 1 END) END)/
(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1) END)*100,'FM990.00') AS WSS2LV,
to_char( (CASE WHEN SUM(CASE wzlb WHEN '其他类' THEN 1 END) is NULL THEN 0 ELSE SUM(CASE WHEN wzlb = '其他类' THEN 1 END) END)/
(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1) END)*100,'FM990.00') AS CY3LV
from( SELECT * FROM( SELECT * FROM (SELECT a.*,NVL(b.WB_QYDF,0) AS WB_QYDF,NVL(ROUND((QYDF*0.8)+(WB_QYDF*0.2) ,2),0) AS ZDF FROM (SELECT * FROM (SELECT GYSMC AS GYSMCID,GYSMC,DQDJ,WZNAME,WZLB,WZPL_ID,WZPL,FDDBR,LXRJDH,DATA_TIME,COUNT(ORG_CODE) AS SUMPROJECT,NVL(SUM(HTJE),0) as SUMHTJE1,ROUND(NVL(SUM(BZLDF)/COUNT(*),0),2) as SUMHTJE2,ROUND(NVL(SUM(JSWCL)/COUNT(*),0),2) as SUMHTJE3,ROUND(NVL((SUM(SFFHHTYDJGJBZ)+SUM(SFYZLCY_CYCLSFJSDW)+SUM(CPZLSFZSYX))/COUNT(*),0),2) as SUMHTJE4,ROUND(NVL(SUM(GCPHJSHFW)/COUNT(*),0),2) as SUMHTJE5,NVL(HX,0) as HX,ROUND(NVL(SUM(QYDF)/COUNT(*),0),2) as QYDF FROM ( SELECT B.WZNAME,a.DATA_TIME,a.GYSMC,a.DQDJ,b.WZ_TYPE AS WZLB,a.WZPL AS WZPL_ID,b.WZNAME as WZPL,a.FDDBR,a.LXRJDH,a.ORG_CODE,a.HTJE,a.BZLDF,a.JSWCL,a.SFFHHTYDJGJBZ,a.SFYZLCY_CYCLSFJSDW,a.CPZLSFZSYX,a.GCPHJSHFW,a.HX,a.SFCZJSZY,case when (NVL(HX,0)=0) then 0 else NVL(NVL(BZLDF,0)+NVL(JSWCL,0)+NVL(SFCZJSZY,0)+NVL(SFFHHTYDJGJBZ,0)+NVL(SFYZLCY_CYCLSFJSDW,0)+NVL(CPZLSFZSYX,0)+NVL(GCPHJSHFW,0)+NVL(HX,0),0) end AS QYDF FROM ZT_WZB_FGSSJY_JX a LEFT JOIN (SELECT * FROM ZT_WZB_WZINFO) b ON a.WZPL=b.ID WHERE 1=1 AND GYSMC is not null AND WZPL is not null AND SFLRZBHS = '是' AND ORG_CODE IN (SELECT DISTINCT ORG_CODE FROM ZT_WZB_FGSSJY_JX)) GROUP BY GYSMC,DQDJ,WZNAME,WZLB,WZPL_ID,WZPL,FDDBR,LXRJDH,DATA_TIME,HX) where 1=1 ) a LEFT JOIN ( SELECT * FROM (SELECT c.GYSMC,c.DATA_TIME, case when (NVL(c.HX,0)=0) then 0 else NVL(NVL(c.QYXZ,0)+NVL(c.ZCSJ,0)+NVL(c.QYNSXYZK,0)+NVL(c.SFFX,0)+NVL(c.HX,0),0) end AS WB_QYDF FROM ZT_WZB_FGSSJY_WB c WHERE 1=1 ) )b ON a.GYSMC = b.GYSMC AND a.DATA_TIME = b.DATA_TIME) union SELECT * FROM (SELECT a.*,NVL(b.WB_QYDF,0) AS WB_QYDF,NVL(ROUND((QYDF*0.8)+(WB_QYDF*0.2) ,2),0) AS ZDF FROM (SELECT * FROM (SELECT GYSMC AS GYSMCID,GYSMC,DQDJ,WZNAME,WZLB,WZPL_ID,WZPL,FDDBR,LXRJDH,DATA_TIME,COUNT(ORG_CODE) AS SUMPROJECT,NVL(SUM(HTJE),0) as SUMHTJE1,ROUND(NVL(SUM(BZLDF)/COUNT(*),0),2) as SUMHTJE2,ROUND(NVL(SUM(SFYZZJS_WJSJS)/COUNT(*),0),2) as SUMHTJE3,ROUND(NVL((SUM(SFFHHTYDJGJBZ)+SUM(SFYZLCY_CYCLSFJSDW)+SUM(CPZLZMSFZSYX))/COUNT(*),0),2) as SUMHTJE4,ROUND(NVL(SUM(GCPHJSHFW)/COUNT(*),0),2) as SUMHTJE5,NVL(HX,0) as HX,ROUND(NVL(SUM(QYDF)/COUNT(*),0),2) as QYDF FROM (SELECT B.WZNAME,a.DATA_TIME,a.GYSMC,a.DQDJ,b.WZ_TYPE AS WZLB,a.WZPL AS WZPL_ID,b.WZNAME as WZPL,a.FDDBR,a.LXRJDH,a.ORG_CODE,a.HTJE,a.BZLDF,a.SFYZZJS_WJSJS,a.SFFHHTYDJGJBZ,a.SFYZLCY_CYCLSFJSDW,a.CPZLZMSFZSYX,a.GCPHJSHFW,a.HX,case when (NVL(HX,0)=0) then 0 else NVL(NVL(BZLDF,0)+NVL(SFYZZJS_WJSJS,0)+NVL(SFFHHTYDJGJBZ,0)+NVL(SFYZLCY_CYCLSFJSDW,0)+NVL(CPZLZMSFZSYX,0)+NVL(GCPHJSHFW,0)+NVL(HX,0),0) end AS QYDF FROM ZT_WZB_FGSSJY_WZ a LEFT JOIN (SELECT * FROM ZT_WZB_WZINFO) b ON a.WZPL=b.ID WHERE 1=1 AND GYSMC is not null AND WZPL is not null AND SFLRZBHS = '是' AND ORG_CODE IN (SELECT DISTINCT ORG_CODE FROM ZT_WZB_FGSSJY_WZ)) GROUP BY GYSMC,DQDJ,WZNAME,WZLB,WZPL_ID,WZPL,FDDBR,LXRJDH,DATA_TIME,HX) where 1=1) a LEFT JOIN ( SELECT * FROM (SELECT c.GYSMC,c.DATA_TIME, case when (NVL(c.HX,0)=0) then 0 else NVL(NVL(c.QYXZ,0)+NVL(c.ZCSJ,0)+NVL(c.QYNSXYZK,0)+NVL(c.SFFX,0)+NVL(c.HX,0),0) end AS WB_QYDF FROM ZT_WZB_FGSSJY_WB c WHERE 1=1
) )b ON a.GYSMC = b.GYSMC AND a.DATA_TIME = b.DATA_TIME)WHERE 1=1)
WHERE 1=1 and substr(data_time,0,7)='2021-03' )