問題描述

有表及數據如下

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