mysql分组然后统计某个值的百分比sql实现

问题: mysql如何遍历某个字段所有值并计算比例

解决问题

1.构造数据
INSERT INTO `test`.`test` (`id`, `num`, `num2`, `branch_id`, `origin_count`) VALUES ('1', '172', '10', '1', '7');
INSERT INTO `test`.`test` (`id`, `num`, `num2`, `branch_id`, `origin_count`) VALUES ('3', '880', '56', '1', '16');
INSERT INTO `test`.`test` (`id`, `num`, `num2`, `branch_id`, `origin_count`) VALUES ('5', '197', '35.5', '1', '21');
INSERT INTO `test`.`test` (`id`, `num`, `num2`, `branch_id`, `origin_count`) VALUES ('7', '290', '34', '2', '34');
INSERT INTO `test`.`test` (`id`, `num`, `num2`, `branch_id`, `origin_count`) VALUES ('9', '334', '24', '2', '23');

2.sql实现
-- 使用自连接
SELECT
SUM(t.num) num,
SUM(t.num2) num2,
t.branch_id,
-- 根据结果我就想到了要用group_concat
-- ROUND(t.origin_count / r.origin_count, 2)函数保留两位小数
-- SEPARATOR '|'使用|符号链接数据
GROUP_CONCAT(ROUND(t.origin_count / r.origin_count, 2) SEPARATOR '|') origin_count
FROMtest t,
(SELECT branch_id,SUM(origin_count) origin_count FROM test GROUP BY branch_id ) r
WHERE t.branch_id = r.branch_id
GROUP BY t.branch_id ;

截图附件