两列中出现的MySQL计数

Question

我有 ff。结构:

+----+---------+---------+---------+---------+---------+--------+
| id | player1 | player2 |   win   |    lose |   LEAVE | STATUS |
+----+---------+---------+---------+---------+---------+--------+
|  1 |  151663 |  150000 |  151663 |  150000 |       0 |      1 |
|  2 |  150000 |  151663 |  150000 |  151663 |       0 |      1 |
|  3 |  151663 |  150000 |  151663 |  150000 |       0 |      1 |
+----+---------+---------+---------+---------+---------+--------+

如果我需要如下结果，如何生成查询？

+--------+-----+------+-------+
| player | win | lose | leave |
+--------+-----+------+-------+
| 150000 |   1 |    2 |     0 |
| 151663 |   2 |    1 |     0 |
+--------+-----+------+-------+

Answer 1

您可以使用union all 和聚合:

select player, sum(win) as wins, sum(lose) as losses,
       sum(leave) as leaves
from ((select win as player, 1 as win, 0 as lose, 0 as leave
       from ff
       where win <> 0
      ) union all
      (select lose as player, 0 as win, 1 as lose, 0 as leave
       from ff
       where lose <> 0
      ) union all
      (select leave as player, 0 as win, 0 as lose, 1 as leave
       from ff
       where leave <> 0
      )
     ) wll
group by player;