mysql - MySQL中每两列的分组数据计算

Question

我想从下表数据中得到他的总胜场数，他参加的比赛总数以及每个球员每年的胜/场比。

+---------+---------+--------+--------+--------+------+
| player1 | player2 | score1 | score2 | winner | year |
+---------+---------+--------+--------+--------+------+
|  100000 |  100125 |      4 |      0 | 100000 | 2016 |
|  100125 |  100126 |      4 |      0 | 100125 | 2016 |
|  100130 |  100000 |      0 |      4 | 100000 | 2017 |
|  100125 |  100130 |      4 |      0 | 100125 | 2017 |
+---------+---------+--------+--------+--------+------+

所以请求的查询通常应该返回这样的行:

+--------+---------------+------+-------+------+
| player | total_matches | wins | ratio | year |
+--------+---------------+------+-------+------+
| 100000 |             1 |    1 | 1     | 2016 |
| 100000 |             1 |    1 | 1     | 2017 |
| 100125 |             2 |    1 | 0.5   | 2016 |
| 100125 |             1 |    1 | 1     | 2017 |
| 100126 |             1 |    0 | 0     | 2016 |
| 100130 |             2 |    0 | 0     | 2017 |
+--------+---------------+------+-------+------+

如果我按获胜者和年份分组，我可以轻松获得每年的获胜次数，但我无法获得 total_matches 和比率。还有其他方法吗？提前致谢。

Answer 1

试试这个:

SELECT player, 
       COUNT(*) AS total_matches, 
       SUM(player = winner) AS wins,
       SUM(player = winner) / COUNT(*) AS ratio,
       `year`
FROM (
   SELECT player1 AS player, winner, `year`
   FROM mytable

   UNION ALL 

   SELECT player2, winner, `year`
   FROM mytable) AS t
GROUP BY player, `year`   

查询使用 UNION ALL 来获得一个表，该表在单个列中包含所有 player1、player2 值。它还使用 条件聚合 来计算 wins 字段:SUM(player = winner) 将仅对那些具有 player 的记录求和= 获胜者。

Demo here