mysql 根据时间，时间段，统计不同审批状态的占比

mysql 

SELECT 
IFNULL(ROUND((SUM(IF(psmd.approve_state='0',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') refusal,-- 不同意
IFNULL(ROUND((SUM(IF(psmd.approve_state='1',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') agree, -- 同意
IFNULL(ROUND((SUM(IF(psmd.approve_state='2',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') notDelivered, -- 未送达
IFNULL(ROUND((SUM(IF(psmd.approve_state='-1',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') pending -- 待处理
FROM 
pb_subscribe_message psm RIGHT JOIN  pb_subscribe_message_details psmd 
ON psm.subscribe_code=psmd.subscribe_code 
WHERE 

-- 下列条件，可以用mybatis的动态sql做if判断
DATE_FORMAT(psm.subscribe_date,'%Y-%m') between '2019-01' AND '2021-05'-- 时间范围
-- psm.subscribe_date LIKE '%2021-05%' -- 时间

-- DATE_FORMAT(psm.subscribe_date,'%Y') = DATE_FORMAT(now(),'%Y') -- 当前年份,查询今年全年
-- YEAR(psm.subscribe_date) = YEAR(now())  -- 当前年份,查询今年全年

mybatis 

 SELECT
        IFNULL(ROUND((SUM(IF(psmd.approve_state='0',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') refusal,-- 不同意
        IFNULL(ROUND((SUM(IF(psmd.approve_state='1',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') agree, -- 同意
        IFNULL(ROUND((SUM(IF(psmd.approve_state='2',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') notDelivered, -- 未送达
        IFNULL(ROUND((SUM(IF(psmd.approve_state='-1',1,0)) / COUNT(psmd.approve_state is not null))*100,2),'0') pending -- 待处理
        FROM
        pb_subscribe_message psm RIGHT JOIN  pb_subscribe_message_details psmd
        ON psm.subscribe_code=psmd.subscribe_code
        WHERE
        <if test="date != null and date != '' and date != 'yearly'">
        -- 动态的时间格式，为了支持年，年月，年月日的查询
            DATE_FORMAT(psm.subscribe_date,#{dateFormat}) = #{date}
        </if>
        <if test="date == 'yearly'">
            DATE_FORMAT(psm.subscribe_date,'%Y') = DATE_FORMAT(now(),'%Y')
        </if>
        <if test="startDate != null and startDate != '' and endDate != null and endDate != ''">
            DATE_FORMAT(psm.subscribe_date,#{startFormat}) between #{startDate} AND #{endDate}
        </if>