MySQL 需要使用 GROUP BY column_a 的查询来忽略 column_b 的重复项

Question

我正在尝试获取用户赢得的独特奖品数量的报告。 IE。玩家如何赢得所有 3 个奖品，有多少人赢得 2 个奖品等。

+--------+--------+
| player | prize  |
+--------+--------+
|   1    |   1    |
+--------+--------+
|   1    |   1    |
+--------+--------+
|   1    |   2    |
+--------+--------+
|   1    |   3    |
+--------+--------+
|   2    |   1    |
+--------+--------+
|   2    |   2    |
+--------+--------+

我需要的报告应该是这样的:

+-----------+------------+
| prize_qty | player_qty |
+-----------+------------+
|      3    |      1     |
+-----------+------------+
|      2    |      1     |
+-----------+------------+

以下代码关闭:

SELECT DISTINCT COUNT(*) as player_qty, prize_qty FROM 
(SELECT count( * ) AS prize_qty FROM `prizes` GROUP BY player)
as t1 GROUP BY player_qty

但它返回这个:

+-----------+------------+
| prize_qty | player_qty |
+-----------+------------+
|      4    |      1     |
+-----------+------------+
|      2    |      1     |
+-----------+------------+

我需要它来忽略玩家 #1 两次赢得 #1 奖品，但我不确定如何进一步消除重复项。

Answer 1

你说

How players have won all 3 prizes, how many have won 2 prizes, etc..

和

I need it to ignore that player #1 won prize #1 twice

那么，在排除玩家不止一次赢得同一个奖品这一事实后，您不应该计算每个奖品的玩家数吗？

SELECT COUNT(*) AS player_qty, prize AS prize_qty
FROM
  (SELECT DISTINCT prize, player FROM prizes) AS T1
GROUP BY prize