自分のためのメモ
System
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| innodb_version | 5.6.22 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.22 |
| version_comment | Homebrew |
| version_compile_machine | x86_64 |
| version_compile_os | osx10.9 |
+-------------------------+----------+
7 rows in set (0.01 sec)
What happens?
ユーザのスコアテーブルを考えましょう
CREATE TABLE IF NOT EXISTS `scores` (
`id` int(10) unsigned NOT NULL,
`score` double NOT NULL,
`user_id` int(11) NOT NULL
);
INSERT INTO `scores` (`id`, `score`, `user_id`) VALUES
(1, 2, 1),
(2, 0.2, 2),
(3, 3, 2),
(4, 0.2, 2);
mysql> SELECT * FROM `scores`;
+----+-------+---------+
| id | score | user_id |
+----+-------+---------+
| 1 | 2 | 1 |
| 2 | 0.2 | 2 |
| 3 | 3 | 2 |
| 4 | 0.2 | 2 |
+----+-------+---------+
4 rows in set (0.00 sec)
合計しましょう
mysql> SELECT SUM(`score`) AS `total_score` FROM `scores`;
+-------------+
| total_score |
+-------------+
| 5.4 |
+-------------+
1 row in set (0.00 sec)
想定どおり
ユーザ毎に合計しましょう
mysql> SELECT SUM(`score`) AS `total_score_per_user`, `user_id` FROM `scores` GROUP BY `user_id`;
+----------------------+---------+
| total_score_per_user | user_id |
+----------------------+---------+
| 2 | 1 |
| 3.4000000000000004 | 2 |
+----------------------+---------+
2 rows in set (0.00 sec)
！！！
Why?
FLOAT、DOUBLE はおおよその値である（5.0.3 より前では DECIMAL は文字列として保存されるが、計算は FLOAT として計算される）ため