mysql如何计算每项权重占比

Question

有表及数据如下

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 |
+--------+--------------+

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

Answer 1

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

Answer 2

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

Answer 3

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 |
+--------+-------------+