1SELECT2 p.user_id,3MAX(u.player_id)AS player_id,4SUM(u.mail_all)AS mail_all
5FROM player AS p
6INNERJOIN user_score AS u ON p.player_id = u.player_id
7GROUPBY p.user_id;
実行結果
mysql> CREATE TABLE user_score (
-> player_id INT,
-> mail_all INT
-> );
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql> CREATE TABLE player (
-> user_id INT,
-> player_id INT
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO user_score VALUES
-> (5, 9),
-> (9, 9),
-> (10, 9),
-> (11, 9),
-> (12, 9),
-> (13, 9),
-> (14, 9),
-> (15, 9),
-> (16, 9);
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO player VALUES
-> (1, 0),
-> (3, 5),
-> (1, 9),
-> (1, 10),
-> (3, 12),
-> (3, 13);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT
-> p.user_id,
-> MAX(u.player_id) AS player_id,
-> SUM(u.mail_all) AS mail_all
-> FROM player AS p
-> INNER JOIN user_score AS u ON p.player_id = u.player_id
-> GROUP BY p.user_id;
+---------+-----------+----------+
| user_id | player_id | mail_all |
+---------+-----------+----------+
| 1 | 10 | 18 |
| 3 | 13 | 27 |
+---------+-----------+----------+
2 rows in set (0.00 sec)