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>