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 |+--------+--------------+
只有第三种这一种方式吗?有没更简单的方式?
问题解答
回答1:SELECT weight,weight/(select sum(weight) from weight_test) from weight_test;
回答2:把my.ini中的sql_mode=only_full_group_by这个去掉再尝试第一个吧
回答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 |+--------+-------------+
相关文章:
1. nginx中rewrite和redirect有什么区别呢?2. 网页爬虫 - Python:爬虫的中文编码问题?3. mysql - 索引过滤性不好是由什么原因引起的,应该怎么解决4. javascript - vue生成一维码?求助!!!!!急5. javascript - H5页面怎么查看console信息?6. javascript - 修改表单多选项时和后台同事配合的问题。7. tornado - python使用yield是否能保证协程的顺序性?8. node.js - 用node开启的UDP端口,远程无法访问9. angular.js - items.query is not a function这是怎么回事10. angular.js - angularjs 怎么封装 upload 上传
