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

從weight_test選擇體重，體重/（從weight_test選擇總和（體重））；

Answer 2

把my.ini中的sql_mode=only_full_group_by這個去掉再試試第一個吧

Answer 3

雷雷