mysql - 多列计数

Question

我在编写对 2 列中所有值的出现次数求和的查询时遇到问题。我有一个具有以下结构的表:

+-----------+------------+
| player1ID | player2ID  |
+-----------+------------+
| 1         | 2          |
| 1         | 3          |
| 2         | 3          |
| 1         | 4          |
+-----------+------------+

运行查询后我想要一个这样的结果表:

+-----------+------------+
| playerID  | count      |
+-----------+------------+
| 1         | 3          |
| 2         | 2          |
| 3         | 2          |
| 4         | 1          |
+-----------+------------+

我试过下面的查询

select g1.player1ID, g1.count1 + g2.count2 
from 
    (select player1ID, count(*) from table group by player1ID) as g1, 
    (select player2ID, count(*) from table group by player2ID) as g2 
where player1ID = player2ID

但这只会在玩家出现在两列(player1ID 和 player2ID)中时给出计数，但如果它只出现在其中一列中则不会出现。

Answer 1

你可以使用 union在这样的派生表中:

select player, count(*) as count
from (
  select player1id player from table1
  union all
  select player2id player from table1
) sub
group by player;

Sample SQL Fiddle