ID_, PROJECT_ID_, FILL_ID_, OBJECTIVES_MEASURES_ID_, PUSH_PROGRESS_, PUSH_DATE_, COMPLIANCE_RATE_, MONTH_RATE_, REMARKS_, IS_DELE_, CREATE_BY_, CREATE_NAME_, CREATE_ORG_ID_, CREATE_ORG_NAME_, CREATE_DEPT_ID_, CREATE_DEPT_NAME_, CREATE_TIME_, UPDATE_BY_, UPDATE_NAME_, UPDATE_ORG_ID_, UPDATE_ORG_NAME_, UPDATE_DEPT_ID_, UPDATE_DEPT_NAME_, UPDATE_TIME_, LAST_TIME_ SELECT nvl(trunc(sum( complianceRate)/count(id)*100.0,2),0)as complianceRate, nvl(count(id),0) AS num from ( SELECT temp.sumPushProgress/d.YEAR_VALUE_ AS complianceRate, m.ID_ as projectId, temp.id as id FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m JOIN ( SELECT o.PROJECT_ID_ AS projectId,o.ID_ AS id,P.PUSH_PROGRESS_ AS sumPushProgress FROM BIZ_PILOT_QUANTITATIVE_OBJECTIVES o LEFT JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES_PUSH p ON o.ID_ = p.OBJECTIVES_MEASURES_ID_ WHERE p.FILL_ID_ IN ( SELECT ID_ FROM(SELECT *,ROW_NUMBER() OVER ( PARTITION BY PROJECT_ID_, FILL_YEAR_, FILL_MONTH_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND FLOW_TYPE_ = 1 AND STATUS_ = 5 AND FILL_YEAR_ = #{year} AND FILL_MONTH_ = #{month} ) WHERE num = 1 AND p.IS_DELE_ = '0') )temp ON m.ID_ = temp.projectId JOIN BIZ_PILOT_OBJECTIVES_DETAILS d ON temp.id = d.OBJECTIVES_ID_ AND m.SEND_STATUS_ = 1 AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND m.IS_DELE_='0' AND m.ID_=#{projectId} ) SELECT nvl(if(trunc(sum(overNum)*1.0/count(id)*100.0,2)>100.0,100.0,trunc(sum(overNum)*1.0/count(id)*100.0,2)),0)as complianceRate,sum(overNum) as num from ( SELECT ( case #{month} when 1 then if(pushProgress>=JAN_VALUE_,1,0) when 2 then if(pushProgress>=FEB_VALUE_,1,0) when 3 then if(pushProgress>=MAR_VALUE_,1,0 ) when 4 then if(pushProgress>=APR_VALUE_,1,0) when 5 then if(pushProgress>=MAY_VALUE_,1,0) when 6 then if(pushProgress>=JUN_VALUE_,1,0) when 7 then if(pushProgress>=JUL_VALUE_,1,0 ) when 8 then if(pushProgress>=AUG_VALUE_,1,0 ) when 9 then if(pushProgress>=SEP_VALUE_,1,0) when 10 then if(pushProgress>=OCT_VALUE_,1,0 ) when 11 then if( pushProgress>=NOV_VALUE_,1,0) when 12 then if(pushProgress>=DEC_VALUE_,1,0 ) end) as overNum, m.ID_ as projectId, temp.id as id FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m JOIN ( SELECT o.PROJECT_ID_ AS projectId,o.ID_ AS id,P.PUSH_PROGRESS_ AS pushProgress FROM BIZ_PILOT_QUANTITATIVE_OBJECTIVES o LEFT JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES_PUSH p ON o.ID_ = p.OBJECTIVES_MEASURES_ID_ WHERE p.FILL_ID_ IN ( SELECT ID_ FROM(SELECT *,ROW_NUMBER() OVER ( PARTITION BY PROJECT_ID_, FILL_YEAR_, FILL_MONTH_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND FLOW_TYPE_ = 1 AND STATUS_ = 5 AND FILL_YEAR_ = #{year} AND FILL_MONTH_ = #{month} ) WHERE num = 1 AND p.IS_DELE_ = '0') )temp ON m.ID_ = temp.projectId JOIN BIZ_PILOT_OBJECTIVES_DETAILS d ON temp.id = d.OBJECTIVES_ID_ AND m.SEND_STATUS_ = 1 AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND m.IS_DELE_='0' AND m.ID_=#{projectId} ) SELECT nvl(trunc(sum(complianceRate)/count(id)*100.0,2),0)as newComplianceRate, nvl(count(id),0) AS num, projectId, projectName, rank()over(order by nvl(trunc(sum(complianceRate)/count(id)*100.0,2),0) desc) AS no from ( SELECT temp.sumPushProgress/d.YEAR_VALUE_ AS complianceRate, m.ID_ as projectId, temp.id as id, m.PROJECT_NAME_ as projectName FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m JOIN ( SELECT o.PROJECT_ID_ AS projectId,o.ID_ AS id,P.PUSH_PROGRESS_ AS sumPushProgress FROM BIZ_PILOT_QUANTITATIVE_OBJECTIVES o LEFT JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES_PUSH p ON o.ID_ = p.OBJECTIVES_MEASURES_ID_ WHERE p.FILL_ID_ IN ( SELECT ID_ FROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY PROJECT_ID_, FILL_YEAR_, FILL_MONTH_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND FLOW_TYPE_ = 1 AND STATUS_ = 5 AND FILL_YEAR_ = #{year} AND FILL_MONTH_ = #{month} ) WHERE num = 1 AND p.IS_DELE_ = '0') )temp ON m.ID_ = temp.projectId JOIN BIZ_PILOT_OBJECTIVES_DETAILS d ON temp.id = d.OBJECTIVES_ID_ AND m.SEND_STATUS_ = 1 AND m.SEND_STATUS_ = 1 AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND m.IS_DELE_='0' ) group by projectId,projectName order by no SELECT temp.REFORM_QUANTITATIVE_OBJECTIVES_ AS indexName,temp.REFORM_QUANTITATIVE_OBJECTIVES_UNIT_ AS indexUnit,d.YEAR_VALUE_ AS yearValue,temp.PUSH_PROGRESS_ AS indexValue, (case #{month} when 1 THEN IF(temp.PUSH_PROGRESS_>=d.JAN_VALUE_,IF(temp.PUSH_PROGRESS_>d.JAN_VALUE_,1,2),3) when 2 THEN IF(temp.PUSH_PROGRESS_>=d.FEB_VALUE_,IF(temp.PUSH_PROGRESS_>d.FEB_VALUE_,1,2),3) when 3 THEN IF(temp.PUSH_PROGRESS_>=d.MAR_VALUE_,IF(temp.PUSH_PROGRESS_>d.MAR_VALUE_,1,2),3) when 4 THEN IF(temp.PUSH_PROGRESS_>=d.APR_VALUE_,IF(temp.PUSH_PROGRESS_>d.APR_VALUE_,1,2),3) when 5 THEN IF(temp.PUSH_PROGRESS_>=d.MAY_VALUE_,IF(temp.PUSH_PROGRESS_>d.MAY_VALUE_,1,2),3) when 6 THEN IF(temp.PUSH_PROGRESS_>=d.JUN_VALUE_,IF(temp.PUSH_PROGRESS_>d.JUN_VALUE_,1,2),3) when 7 THEN IF(temp.PUSH_PROGRESS_>=d.JUL_VALUE_,IF(temp.PUSH_PROGRESS_>d.JUL_VALUE_,1,2),3) when 8 THEN IF(temp.PUSH_PROGRESS_>=d.AUG_VALUE_,IF(temp.PUSH_PROGRESS_>d.AUG_VALUE_,1,2),3) when 9 THEN IF(temp.PUSH_PROGRESS_>=d.SEP_VALUE_,IF(temp.PUSH_PROGRESS_>d.SEP_VALUE_,1,2),3) when 10 THEN IF(temp.PUSH_PROGRESS_>=d.OCT_VALUE_,IF(temp.PUSH_PROGRESS_>d.OCT_VALUE_,1,2),3) when 11 THEN IF(temp.PUSH_PROGRESS_>=d.NOV_VALUE_,IF(temp.PUSH_PROGRESS_>d.NOV_VALUE_,1,2),3) when 12 THEN IF(temp.PUSH_PROGRESS_>=d.DEC_VALUE_,IF(temp.PUSH_PROGRESS_>d.DEC_VALUE_,1,2),3) END)as isOver from BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m join (SELECT o.*,p.PUSH_PROGRESS_ FROM BIZ_PILOT_QUANTITATIVE_OBJECTIVES o LEFT JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES_PUSH p ON o.ID_ = p.OBJECTIVES_MEASURES_ID_ WHERE p.FILL_ID_ IN ( SELECT ID_ FROM(SELECT*,ROW_NUMBER() OVER (PARTITION BY PROJECT_ID_, FILL_YEAR_, FILL_MONTH_ ORDER BY CREATE_TIME_ DESC) AS num FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND FLOW_TYPE_ = 1 AND STATUS_=5 AND FILL_YEAR_=#{year} AND FILL_MONTH_=#{month} ) WHERE num = 1 AND p.IS_DELE_='0' )) as temp on m.ID_=temp.PROJECT_ID_ join BIZ_PILOT_OBJECTIVES_DETAILS d on temp.ID_=d.OBJECTIVES_ID_ WHERE 1=1 AND m.SEND_STATUS_ = 1 AND m.IS_DELE_ = '0' AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND m.ID_ = #{projectId} SELECT sum(case #{month} when 1 THEN IF(temp.PUSH_PROGRESS_>d.JAN_VALUE_,1,0) when 2 THEN IF(temp.PUSH_PROGRESS_>d.FEB_VALUE_,1,0) when 3 THEN IF(temp.PUSH_PROGRESS_>d.MAR_VALUE_,1,0) when 4 THEN IF(temp.PUSH_PROGRESS_>d.APR_VALUE_,1,0) when 5 THEN IF(temp.PUSH_PROGRESS_>d.MAY_VALUE_,1,0) when 6 THEN IF(temp.PUSH_PROGRESS_>d.JUN_VALUE_,1,0) when 7 THEN IF(temp.PUSH_PROGRESS_>d.JUL_VALUE_,1,0) when 8 THEN IF(temp.PUSH_PROGRESS_>d.AUG_VALUE_,1,0) when 9 THEN IF(temp.PUSH_PROGRESS_>d.SEP_VALUE_,1,0) when 10 THEN IF(temp.PUSH_PROGRESS_>d.OCT_VALUE_,1,0) when 11 THEN IF(temp.PUSH_PROGRESS_>d.NOV_VALUE_,1,0) when 12 THEN IF(temp.PUSH_PROGRESS_>d.DEC_VALUE_,1,0) END)as excessCount, sum(case #{month} when 1 THEN IF(temp.PUSH_PROGRESS_=d.JAN_VALUE_,1,0) when 2 THEN IF(temp.PUSH_PROGRESS_=d.FEB_VALUE_,1,0) when 3 THEN IF(temp.PUSH_PROGRESS_=d.MAR_VALUE_,1,0) when 4 THEN IF(temp.PUSH_PROGRESS_=d.APR_VALUE_,1,0) when 5 THEN IF(temp.PUSH_PROGRESS_=d.MAY_VALUE_,1,0) when 6 THEN IF(temp.PUSH_PROGRESS_=d.JUN_VALUE_,1,0) when 7 THEN IF(temp.PUSH_PROGRESS_=d.JUL_VALUE_,1,0) when 8 THEN IF(temp.PUSH_PROGRESS_=d.AUG_VALUE_,1,0) when 9 THEN IF(temp.PUSH_PROGRESS_=d.SEP_VALUE_,1,0) when 10 THEN IF(temp.PUSH_PROGRESS_=d.OCT_VALUE_,1,0) when 11 THEN IF(temp.PUSH_PROGRESS_=d.NOV_VALUE_,1,0) when 12 THEN IF(temp.PUSH_PROGRESS_=d.DEC_VALUE_,1,0) END)as reachedCount, sum(case #{month} when 1 THEN IF(temp.PUSH_PROGRESS_d.JAN_VALUE_,1,0) when 2 THEN IF(temp.PUSH_PROGRESS_d.FEB_VALUE_,1,0) when 3 THEN IF(temp.PUSH_PROGRESS_d.MAR_VALUE_,1,0) when 4 THEN IF(temp.PUSH_PROGRESS_d.APR_VALUE_,1,0) when 5 THEN IF(temp.PUSH_PROGRESS_d.MAY_VALUE_,1,0) when 6 THEN IF(temp.PUSH_PROGRESS_d.JUN_VALUE_,1,0) when 7 THEN IF(temp.PUSH_PROGRESS_d.JUL_VALUE_,1,0) when 8 THEN IF(temp.PUSH_PROGRESS_d.AUG_VALUE_,1,0) when 9 THEN IF(temp.PUSH_PROGRESS_d.SEP_VALUE_,1,0) when 10 THEN IF(temp.PUSH_PROGRESS_d.OCT_VALUE_,1,0) when 11 THEN IF(temp.PUSH_PROGRESS_d.NOV_VALUE_,1,0) when 12 THEN IF(temp.PUSH_PROGRESS_d.DEC_VALUE_,1,0) END)as notReachedCount from BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m join (SELECT o.*,p.PUSH_PROGRESS_ FROM BIZ_PILOT_QUANTITATIVE_OBJECTIVES o LEFT JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES_PUSH p ON o.ID_ = p.OBJECTIVES_MEASURES_ID_ WHERE p.FILL_ID_ IN ( SELECT ID_ FROM(SELECT*,ROW_NUMBER() OVER (PARTITION BY PROJECT_ID_, FILL_YEAR_, FILL_MONTH_ ORDER BY CREATE_TIME_ DESC) AS num FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND FLOW_TYPE_ = 1 AND STATUS_=5 AND FILL_YEAR_=#{year} AND FILL_MONTH_=#{month} )WHERE num = 1 AND p.IS_DELE_='0' )) as temp on m.ID_=temp.PROJECT_ID_ join BIZ_PILOT_OBJECTIVES_DETAILS d on temp.ID_=d.OBJECTIVES_ID_ WHERE 1=1 AND m.SEND_STATUS_ = 1 AND m.IS_DELE_ = '0' AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND m.ID_ = #{projectId} SELECT m.ID_ as projectId, o.ID_ as objectIvesId, p.ID_ as objectIvesPushId, f.fill_year_ as fillYear, f.fill_month_ as fillMonth, o.REFORM_QUANTITATIVE_OBJECTIVES_ as content, o.REFORM_QUANTITATIVE_OBJECTIVES_UNIT_ as unit, d.YEAR_VALUE_ as yearValue,( CASE f.fill_month_ WHEN 1 THEN d.JAN_VALUE_ WHEN 2 THEN d.FEB_VALUE_ WHEN 3 THEN d.MAR_VALUE_ WHEN 4 THEN d.APR_VALUE_ WHEN 5 THEN d.MAY_VALUE_ WHEN 6 THEN d.JUN_VALUE_ WHEN 7 THEN d.JUL_VALUE_ WHEN 8 THEN d.AUG_VALUE_ WHEN 9 THEN d.SEP_VALUE_ WHEN 10 THEN d.OCT_VALUE_ WHEN 11 THEN d.NOV_VALUE_ WHEN 12 THEN d.DEC_VALUE_ ELSE 0 END ) AS monthValue, p.PUSH_PROGRESS_ as value FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m LEFT JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES o ON m.ID_ = o.PROJECT_ID_ JOIN BIZ_PILOT_QUANTITATIVE_OBJECTIVES_PUSH AS p ON o.ID_ = p.OBJECTIVES_MEASURES_ID_ JOIN ( SELECT*FROM ( SELECT *,row_number() over ( PARTITION BY PROJECT_ID_, FILL_YEAR_, FILL_MONTH_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND STATUS_ = 5 AND FLOW_TYPE_ = 1 AND FILL_YEAR_=#{year} AND FILL_MONTH_ #{year} ) WHERE num = 1 ) AS f ON p.FILL_ID_ = f.ID_ JOIN BIZ_PILOT_OBJECTIVES_DETAILS d ON o.ID_ = d.OBJECTIVES_ID_ WHERE 1=1 AND m.ID_=#{projectId} AND m.SEND_STATUS_ = 1 AND m.IS_DELE_ = '0' AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1