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. css - .clearfix:after中为什么设置display: table2. java - JPA 中自定义对象和原生对象属性名不一致怎么解决?3. javascript - angular-cli使用ng build --prod 和ng build --prod --aot 打包文件大小相同?4. javascript - Vue和React哪个源码更适合阅读学习?不谈框架优劣,只从学习的角度出发,希望知其所以然。5. java - String s=new String()与String s = ""的区别6. node.js开发微信公众账号设备功能问题,求解答!!7. python - django 按日归档统计订单求解8. java - IDEA 修改方法注释模板9. node.js - Angular-webpack-Starter, 怎么把NodeJS添加进项目里?10. 网站上有个图标改不了了 求帮忙 是用svg写的
