ID_, PILOT_ID_, RECORD_ID_, RESULT_FORM_, RESULT_NAME_, RESULT_DATE_, SOLVE_PROBLEM_, ACTUAL_EFFECT_, RESULT_YEAR_, RESULT_QUARTER_, RESULT_MONTH_, RESULT_DES_, CLASSIFIED_, REMARKS_, IS_DELE_ SELECT t.RESULT_FORM_ achievementType, COUNT(t.id_) AS totalAchievement FROM BIZ_PILOT_ADVANCE_RESULT t INNER JOIN ( SELECT * FROM( SELECT *,ROW_NUMBER()OVER(PARTITION BY PILOT_ID_,UNIT_ID_ ORDER BY CREATE_TIME_ DESC) NUM FROM BIZ_PILOT_ADVANCE_RECORD WHERE IS_DELE_='0'AND STATUS_=12 ) where NUM=1 ) a ON t.RECORD_ID_=a.id_ INNER JOIN BIZ_REFORM_PILOT_MANAGEMENT p ON t.PILOT_ID_=p.id_ WHERE p.is_dele_='0' and a.is_dele_='0' and t.is_dele_='0' AND p.STATUS_=5 and (t.RESULT_YEAR_#{year} or (t.RESULT_YEAR_#{year} and t.RESULT_QUARTER_#{quarter})) and (t.RESULT_YEAR_#{year} and t.RESULT_QUARTER_#{quarter}) and a.UNIT_CODE_ = #{unitCode} group by t.RESULT_FORM_ SELECT r.*, p.PILOT_PROJECT_NAME_, a.UNIT_ID_, a.UNIT_CODE_, a.UNIT_NAME_, a.UNIT_GRADE_, p.PILOT_LEVEL_ FROM BIZ_PILOT_ADVANCE_RESULT r INNER JOIN ( SELECT * FROM( SELECT *,ROW_NUMBER()OVER(PARTITION BY PILOT_ID_,UNIT_ID_ ORDER BY CREATE_TIME_ DESC) NUM FROM BIZ_PILOT_ADVANCE_RECORD WHERE IS_DELE_='0'AND STATUS_=12 ) where NUM=1 ) a ON r.RECORD_ID_=a.id_ INNER JOIN BIZ_REFORM_PILOT_MANAGEMENT p ON r.PILOT_ID_=p.id_ ${ew.customSqlSegment} WHERE and (r.result_year_ #{ew.paramNameValuePairs.year} or (r.result_year_=#{ew.paramNameValuePairs.year} and r.result_quarter_ #{ew.paramNameValuePairs.quarter})) and (r.result_year_ #{ew.paramNameValuePairs.year}) and r.result_year_ #{ew.paramNameValuePairs.quarter} order by r.RESULT_DATE_ desc SELECT t.PILOT_ID_, a.UNIT_ID_, a.UNIT_CODE_, a.UNIT_NAME_, a.UNIT_GRADE_, COUNT(DISTINCT t.id_) AS "totalNum", NVL(COUNT(DISTINCT CASE WHEN dd.value_ = 1 THEN t.id_ END), 0) AS "regimeNum", NVL(COUNT(DISTINCT CASE WHEN dd.value_ = 2 THEN t.id_ END), 0) AS "platformNum", NVL(COUNT(DISTINCT CASE WHEN dd.value_ = 3 THEN t.id_ END), 0) AS "appNum", NVL(COUNT(DISTINCT CASE WHEN dd.value_ = 4 THEN t.id_ END), 0) AS "otherNum" FROM PORTAL_SYS_DICTIONARY_DETAIL dd INNER JOIN PORTAL_SYS_DICTIONARY d ON d.id_=dd.DICTIONARY_ID_ and d.code_='ggsd_cgxs' left join BIZ_PILOT_ADVANCE_RESULT t on to_char(t.RESULT_FORM_)=dd.value_ AND d.flag_=1 AND dd.flag_=1 INNER JOIN BIZ_PILOT_ADVANCE_RECORD a ON t.RECORD_ID_=a.id_ INNER JOIN BIZ_REFORM_PILOT_MANAGEMENT p ON t.PILOT_ID_=p.id_ where p.is_dele_='0' and p.status_=5 and t.is_dele_='0' and a.is_dele_='0' and a.STATUS_=12 and t.PILOT_ID_ = #{pilotId} and (t.RESULT_YEAR_#{year} or (t.RESULT_YEAR_#{year} and t.RESULT_QUARTER_#{quarter})) and a.UNIT_CODE_ = #{unitCode} group by t.PILOT_ID_, a.UNIT_ID_, a.UNIT_CODE_, a.UNIT_NAME_, a.UNIT_GRADE_