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. golang - 用IDE看docker源碼時的小問題2. docker安裝后出現Cannot connect to the Docker daemon.3. 在windows下安裝docker Toolbox 啟動Docker Quickstart Terminal 失敗！4. docker-machine添加一個已有的docker主機問題5. Docker for Mac 創建的dnsmasq容器連不上/不工作的問題6. docker-compose中volumes的問題7. 我在centos容器里安裝docker，也就是在容器里安裝容器，報錯了？8. 為什么我ping不通我的docker容器呢？？？9. docker容器呢SSH為什么連不通呢？10. docker不顯示端口映射呢？
網公網安備