您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

sql加入百分比

 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' )

 


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进