ID_, PROJECT_ID_, MEASURES_ID_, PLAN_YEAR_, PLAN_QUARTER_, PLAN_MONTH_, PLAN_TYPE_, PLAN_CONTENT_, PLANNED_COMPLETION_TIME_, PLAN_STATUS_, PROGRESS_STATUS_, ACTUAL_COMPLETION_TIME, 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 COUNT(*) totalNum, NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 3 ) THEN s.id_ END), 0) AS finishNum, NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 2 ) THEN s.id_ END), 0) AS progressNum, NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ not in(2,3) ) THEN s.id_ END), 0) AS unStartNum FROM BIZ_PILOT_SPECIFIC_SCHEDULE s INNER JOIN BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m ON s.project_id_ = m.id_ WHERE m.is_dele_='0' and m.CLOSING_=1 and m.PROJECT_TYPE_=1 AND s.is_dele_='0' AND s.PLAN_STATUS_ =1 AND s.plan_year_ = #{year} AND s.plan_month_ #{month} AND s.project_id_=#{projectId} SELECT COUNT(*) totalNum, NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 3 and year(s.ACTUAL_COMPLETION_TIME)=#{year} and month(s.ACTUAL_COMPLETION_TIME) #{month}) THEN s.id_ END), 0) AS finishNum, NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 2 ) THEN s.id_ END), 0) AS progressNum, NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ not in(2,3) ) THEN s.id_ END), 0) AS unStartNum, CASE WHEN COUNT(*)=0 then 0 ELSE cast(NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 3 and year(s.ACTUAL_COMPLETION_TIME)=#{year} and month(s.ACTUAL_COMPLETION_TIME) #{month}) THEN s.id_ END), 0)*1.0/COUNT(*) as decimal(10,4))*100 end as finishRate, CASE WHEN COUNT(*)= NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 3 and year(s.ACTUAL_COMPLETION_TIME)=#{year} and month(s.ACTUAL_COMPLETION_TIME) #{month}) THEN s.id_ END), 0) then '1' WHEN COUNT(*)- NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 3 and year(s.ACTUAL_COMPLETION_TIME)=#{year} and month(s.ACTUAL_COMPLETION_TIME) #{month}) THEN s.id_ END), 0)>0 and COUNT(*)- NVL(COUNT(DISTINCT CASE WHEN (s.PROGRESS_STATUS_ = 3 and year(s.ACTUAL_COMPLETION_TIME)=#{year} and month(s.ACTUAL_COMPLETION_TIME) #{month}) THEN s.id_ END), 0) 2 then '2' ELSE '3' end as degree, s.project_id_,m.PROJECT_NAME_ FROM BIZ_PILOT_SPECIFIC_SCHEDULE s INNER JOIN BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m ON s.project_id_ = m.id_ WHERE m.is_dele_='0' and m.CLOSING_=1 and m.PROJECT_TYPE_=1 AND s.is_dele_='0' AND s.PLAN_STATUS_ =1 AND s.plan_year_ = #{year} AND s.plan_month_ #{month} AND m.ID_=#{projectId} group by s.project_id_,m.PROJECT_NAME_ order by degree asc,finishRate desc select dd.name_ typeName,dd.value_ typeCode,NVL(tab.totalNum,0) typeNum from PORTAL_SYS_DICTIONARY_DETAIL dd inner join PORTAL_SYS_DICTIONARY d on dd.DICTIONARY_ID_=d.ID_ and d.code_='yd_jhlx' left join( SELECT COUNT(*) totalNum,s.PLAN_TYPE_ FROM BIZ_PILOT_SPECIFIC_SCHEDULE s INNER JOIN BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m ON s.project_id_ = m.id_ WHERE m.is_dele_='0' and m.CLOSING_=1 and m.PROJECT_TYPE_=1 AND s.is_dele_='0' AND s.PLAN_STATUS_ =1 AND s.plan_year_ = #{year} AND s.plan_month_ #{month} AND s.project_id_=#{projectId} group by s.PLAN_TYPE_ )tab on dd.value_ = tab.PLAN_TYPE_ where dd.flag_=1 and d.flag_=1 order by dd.sn_ SELECT count( S.ID_ ) AS num FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m JOIN BIZ_PILOT_SPECIFIC_SCHEDULE s ON m.ID_ = s.PROJECT_ID_ LEFT JOIN ( SELECT * FROM (SELECT *, ROW_NUMBER() over ( PARTITION BY PLAN_ID_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_MEASURES_PUSH WHERE FILL_ID_ IN ( SELECT ID_ FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND STATUS_ = 5 AND FLOW_TYPE_=2)) temp WHERE temp.num = 1 ) p ON s.ID_ = p.PLAN_ID_ WHERE s.PLAN_STATUS_ = 1 AND s.IS_DELE_ = '0' AND p.IS_DELE_ = '0' AND m.SEND_STATUS_ = 1 AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND s.PROGRESS_STATUS_=3 AND s.PLAN_YEAR_=#{year} AND s.PLAN_MONTH_#{month} AND s.PLAN_TYPE_=#{type} AND m.ID_=#{projectId} SELECT s.* FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m JOIN BIZ_PILOT_SPECIFIC_SCHEDULE s ON m.ID_ = s.PROJECT_ID_ LEFT JOIN ( SELECT * FROM (SELECT *, ROW_NUMBER() over ( PARTITION BY PLAN_ID_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_MEASURES_PUSH WHERE FILL_ID_ IN ( SELECT ID_ FROM BIZ_PILOT_PROJECT_FILL WHERE IS_DELE_ = '0' AND STATUS_ = 5 AND FLOW_TYPE_=2)) temp WHERE temp.num = 1 ) p ON s.ID_ = p.PLAN_ID_ WHERE s.PLAN_STATUS_ = 1 AND s.IS_DELE_ = '0' AND p.IS_DELE_ = '0' AND m.SEND_STATUS_ = 1 AND m.CLOSING_ = 1 AND m.PROJECT_TYPE_ = 1 AND s.PLAN_YEAR_=#{year} AND s.PLAN_MONTH_#{month} AND m.ID_=#{projectId} SELECT m.project_name_ as projectName,m.LEADER_ as leader,m.UNIT_NAME_ as unitName,m.PROJECT_FIELD_ as projectField,m.PROJECT_RUNWAY_ as projectRunWay,p.STATUS_ as status,p.IS_OVER_ as progressStatus,s.PLAN_YEAR_ as planYear,s.PLAN_MONTH_ as planMonth,s.PLAN_TYPE_ as planType,S.PLAN_CONTENT_ as planContent,p.COMPLETED_WORK_ as completedWork,p.PROBLEMS_ as problems,p.WORK_PLAN_ as workPlan FROM BIZ_MUNICIPAL_MAJOR_PROJECT_MANAGEMENT m JOIN BIZ_PILOT_SPECIFIC_SCHEDULE s ON m.ID_ = s.PROJECT_ID_ LEFT JOIN (SELECT * FROM ( SELECT *, ROW_NUMBER() over ( PARTITION BY PLAN_ID_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_PILOT_MEASURES_PUSH) temp WHERE temp.num = 1 ) p ON s.ID_ = p.PLAN_ID_ ${ew.customSqlSegment} order by m.ID_