ID_, DEMAND_TOPIC_, DEMAND_DESCRIPTION_, DEMAND_SOURCE_CHANNEL_, DEMAND_FIELD_, DEMAND_RUNWAY_, DEMAND_ADOPTION_STATUS_, STATUS_, REPORT_UNIT_ID_, REPORT_UNIT_NAME_, 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_DEPT_NAME_, UPDATE_DEPT_ID_, UPDATE_ORG_ID_, UPDATE_ORG_NAME_, UPDATE_TIME_, LAST_TIME_, VERSION_, TENANT_ID_, RESPONSIBLE_UNIT_CODE_, RESPONSIBLE_UNIT_GRADE_, DEMAND_DATE_, DEMAND_YEAR_, DEMAND_MONTH_, DEMAND_QUARTER_, AUDIT_DATE_, AUDIT_YEAR_, AUDIT_QUARTER_, AUDIT_MONTH_ SELECT COUNT(*) y, DEMAND_SOURCE_CHANNEL_ x FROM BIZ_REFORM_DEMAND_ANALYSIS_LIBRARY WHERE STATUS_ = 5 and is_dele_='0' and DEMAND_YEAR_ = #{year} AND DEMAND_MONTH_ #{month} GROUP BY DEMAND_SOURCE_CHANNEL_ select a.*, RANK() OVER (ORDER BY a.stopNum DESC) stopRank, RANK() OVER (ORDER BY a.projectNum DESC) projectRank, RANK() OVER (ORDER BY a.listNum DESC) listRank, RANK() OVER (ORDER BY a.libraryNum DESC) libraryRank, RANK() OVER (ORDER BY a.demandNum DESC) demandRank from( select tab.REPORT_UNIT_ID_ AS unitId, tab.REPORT_UNIT_NAME_ AS unitName, tab.RESPONSIBLE_UNIT_CODE_ AS unitCode, tab.RESPONSIBLE_UNIT_GRADE_ AS unitGrade, NVL(tab.demandNum,0) demandNum, NVL(tab.libraryNum,0) libraryNum, NVL(tab.listNum,0) listNum, NVL(tab.projectNum,0) projectNum, NVL(tab.stopNum,0) stopNum, nvl(cast((tab.libraryNum*1.0/tab.demandNum)as decimal(6,4)),0) libraryRate, nvl(cast((tab.listNum*1.0/tab.demandNum)as decimal(6,4)),0) listRate, nvl(cast((tab.projectNum*1.0/tab.demandNum)as decimal(6,4)),0) projectRate, nvl(cast((tab.stopNum*1.0/tab.demandNum)as decimal(6,4)),0) stopRate from ( select count(id_) demandNum, NVL(COUNT(DISTINCT CASE WHEN DEMAND_ADOPTION_STATUS_ = 1 THEN id_ END), 0) libraryNum, NVL(COUNT(DISTINCT CASE WHEN DEMAND_ADOPTION_STATUS_ = 2 THEN id_ END), 0) listNum, NVL(COUNT(DISTINCT CASE WHEN DEMAND_ADOPTION_STATUS_ = 3 THEN id_ END), 0) projectNum, NVL(COUNT(DISTINCT CASE WHEN DEMAND_ADOPTION_STATUS_ = 4 THEN id_ END), 0) stopNum, REPORT_UNIT_ID_,REPORT_UNIT_NAME_,RESPONSIBLE_UNIT_CODE_,RESPONSIBLE_UNIT_GRADE_ from BIZ_REFORM_DEMAND_ANALYSIS_LIBRARY where is_dele_='0' and status_ = 5 and DEMAND_YEAR_ = #{year} AND DEMAND_MONTH_ #{month} group by REPORT_UNIT_ID_,REPORT_UNIT_NAME_,RESPONSIBLE_UNIT_CODE_,RESPONSIBLE_UNIT_GRADE_ ) tab where 1=1 AND tab.RESPONSIBLE_UNIT_CODE_ = #{unitCode} ) a SELECT d.*,l.OPERATE_DATE_ as thisTime FROM BIZ_REFORM_DEMAND_ANALYSIS_LIBRARY d LEFT JOIN ( SELECT * FROM ( SELECT BIZ_ID_, OPERATE_DATE_, row_number() over ( PARTITION BY BIZ_ID_ ORDER BY CREATE_TIME_ DESC ) AS num FROM BIZ_ACHIEVE_STATUS_LOG WHERE STATUS_ = 1 ) WHERE num = 1 )AS l ON d.ID_ = l.BIZ_ID_ ${ew.customSqlSegment} ORDER BY d.status_ asc,d.last_time_ desc