mysql查询展示百分比

涉及知识点：

-- 计算百分比
concat(round(count((`state`=2 and `is_ok`=1) or null)/count(`state`=2 or null)*100,2),'%')

改造前：

<select id="getSameMonthInsuredStatistics"

resultType="com.kofan.renli.vo.escortinsurance.SameMonthStatisticsVo">

SELECT

COUNT(is_pay_injury = '1' OR NULL) as gs_sum,

COUNT(is_pay_injury = '0' OR NULL) as gsw_sum,

COUNT(is_pay_endowment = '1' OR NULL) as ylx_sum,

COUNT(is_pay_endowment = '0' OR NULL) as ylxw_sum,

COUNT(is_pay_medical = '1' OR NULL) as yl_sum,

COUNT(is_pay_medical = '0' OR NULL) as ylw_sum,

COUNT(is_pay_unemployment = '1' OR NULL) as sy_sum,

COUNT(is_pay_unemployment = '0' OR NULL) as syw_sum,

COUNT(is_pay_accumulation = '1' OR NULL) as gjj_sum,

COUNT(is_pay_accumulation = '0' OR NULL) as gjjw_sum

FROM

escort_insurance_month

WHERE

is_del = 0

AND DATE_FORMAT(insurance_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')

</select>

改造后：

<select id="getSameMonthInsuredStatistics"

resultType="com.kofan.renli.vo.escortinsurance.SameMonthStatisticsVo">

SELECT

CONCAT(ROUND(COUNT((IS_PAY_INJURY = '1' ) OR NULL) / COUNT(IS_PAY_INJURY = '1' OR IS_PAY_INJURY = '0' OR NULL) *100 ,2),'%') AS GS_SUM,

CONCAT(ROUND(COUNT((IS_PAY_INJURY = '0' ) OR NULL) / COUNT(IS_PAY_INJURY = '1' OR IS_PAY_INJURY = '0' OR NULL) *100 ,2),'%') AS GSW_SUM,

CONCAT(ROUND(COUNT((IS_PAY_ENDOWMENT = '1' ) OR NULL) / COUNT(IS_PAY_ENDOWMENT = '1' OR IS_PAY_ENDOWMENT = '0' OR NULL) *100 ,2),'%') AS YLX_SUM,

CONCAT(ROUND(COUNT((IS_PAY_ENDOWMENT = '0' ) OR NULL) / COUNT(IS_PAY_ENDOWMENT = '1' OR IS_PAY_ENDOWMENT = '0' OR NULL) *100 ,2),'%') AS YLXW_SUM,

CONCAT(ROUND(COUNT((IS_PAY_MEDICAL = '1' ) OR NULL) / COUNT(IS_PAY_MEDICAL = '1' OR IS_PAY_MEDICAL = '0' OR NULL) *100 ,2),'%') AS YL_SUM,

CONCAT(ROUND(COUNT((IS_PAY_MEDICAL = '0' ) OR NULL) / COUNT(IS_PAY_MEDICAL = '1' OR IS_PAY_MEDICAL = '0' OR NULL) *100 ,2),'%') AS YLW_SUM,

CONCAT(ROUND(COUNT((IS_PAY_UNEMPLOYMENT = '1' ) OR NULL) / COUNT(IS_PAY_UNEMPLOYMENT = '1' OR IS_PAY_UNEMPLOYMENT = '0' OR NULL) *100 ,2),'%') AS SY_SUM,

CONCAT(ROUND(COUNT((IS_PAY_UNEMPLOYMENT = '0' ) OR NULL) / COUNT(IS_PAY_UNEMPLOYMENT = '1' OR IS_PAY_UNEMPLOYMENT = '0' OR NULL) *100 ,2),'%') AS SYW_SUM,

CONCAT(ROUND(COUNT((IS_PAY_ACCUMULATION = '1' ) OR NULL) / COUNT(IS_PAY_ACCUMULATION = '1' OR IS_PAY_ACCUMULATION = '0' OR NULL) *100 ,2),'%') AS GJJ_SUM,

CONCAT(ROUND(COUNT((IS_PAY_ACCUMULATION = '0' ) OR NULL) / COUNT(IS_PAY_ACCUMULATION = '1' OR IS_PAY_ACCUMULATION = '0' OR NULL) *100 ,2),'%') AS GJJW_SUM

FROM

ESCORT_INSURANCE_MONTH

WHERE

IS_DEL = 0

AND DATE_FORMAT(INSURANCE_DATE, '%Y-%M') = DATE_FORMAT(NOW(), '%Y-%M')

</select>

前后差异：

原型图及需求：