先统计总数,再带入计算
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;
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于