mysql如何计算每项权重占比

问题描述

有表及数据如下

select * from weight_test;+----+------+--------+| id | name | weight |+----+------+--------+| 1 | aaa | 10 || 2 | bbb | 20 || 3 | ccc | 30 || 4 | ddd | 40 |+----+------+--------+

想计算每项的权重占比

#尝试一 失败select weight, weight/sum(weight) from weight_test;ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ’test.weight_test.weight’; this is incompatible with sql_mode=only_full_group_by#尝试二 失败select weight, weight/sum(weight) from weight_test group by weight;+--------+--------------------+| weight | weight/sum(weight) |+--------+--------------------+| 10 | 1.0000 || 20 | 1.0000 || 30 | 1.0000 || 40 | 1.0000 |+--------+--------------------+#尝试三 成功select weight, weight/total from weight_test a, (select sum(weight) total from weight_test) b;+--------+--------------+| weight | weight/total |+--------+--------------+| 10 | 0.1000 || 20 | 0.2000 || 30 | 0.3000 || 40 | 0.4000 |+--------+--------------+

只有第三种这一种方式吗？有没更简单的方式？

问题解答

SELECT weight,weight/(select sum(weight) from weight_test) from weight_test;

把my.ini中的sql_mode=only_full_group_by这个去掉再尝试第一个吧

set @sum = (select sum(weight) from weight_test);select @sum;+------+| @sum |+------+| 100 |+------+select weight, weight/@sum from weight_test;+--------+-------------+| weight | weight/@sum |+--------+-------------+| 10 | 0.1000 || 20 | 0.2000 || 30 | 0.3000 || 40 | 0.4000 |+--------+-------------+