mysql 分组计算占比

先统计总数,再带入计算

SET @sum = (SELECT SUM(total) FROM (
SELECT a.external_merchant '规则',a.model_code,COUNT(0) total FROM (
SELECT mr.code,mr.`model_id`
,rm.id,rm.`external_merchant`
,mrd.`model_code`,mrd.`model_name`,mrd.`rule_code`,mrd.`rule_name`
,r.`result` 
FROM `risk_model_result` mr 
INNER JOIN `risk_model_result_detail` mrd ON mr.id = mrd.`model_result_id` 
LEFT JOIN `risk_rule_module` rm ON rm.`model_id`= mr.`model_id` AND rm.`code`=mrd.`model_code` 
LEFT JOIN `risk_rule` r ON r.`rule_module_id` = rm.id AND r.`code`=mrd.`rule_code`
WHERE mr.`result_type`=0 AND mr.`merchant_id`=2 AND mr.`model_id`=5
) a GROUP BY a.model_code) b);
#select @sum;
SELECT a.external_merchant '规则',a.model_code,COUNT(0) total
,CONCAT(CONVERT(TRUNCATE(COUNT(0)/@sum,3)*100,DECIMAL(10,1)),'%') percent
FROM (
SELECT mr.code,mr.`model_id`
,rm.id,rm.`external_merchant`
,mrd.`model_code`,mrd.`model_name`,mrd.`rule_code`,mrd.`rule_name`
,r.`result` 
FROM `risk_model_result` mr 
INNER JOIN `risk_model_result_detail` mrd ON mr.id = mrd.`model_result_id` 
LEFT JOIN `risk_rule_module` rm ON rm.`model_id`= mr.`model_id` AND rm.`code`=mrd.`model_code` 
LEFT JOIN `risk_rule` r ON r.`rule_module_id` = rm.id AND r.`code`=mrd.`rule_code`
WHERE mr.`result_type`=0 AND mr.`merchant_id`=2 AND mr.`model_id`=5
) a GROUP BY a.model_code ORDER BY total DESC;
​