mysql - 选择具有另一个 ID 的最大计数的 ID

Question

我有一个棒球比赛数据库，其中包含一个 PlayerID 和一个 TypeID(比赛类型: double 、三振等)。数据看起来像这样:

+----------+--------+
| playerid | typeid |
+----------+--------+
|        2 |      4 |
|        2 |      4 |
|        2 |      7 |
|        3 |      7 |
|        3 |      7 |
|        3 |      7 |
|        3 |     26 |
|        3 |      7 |

我试图找出哪些球员在每种比赛中发挥得最好。例如。 Jim (PlayerID 3) 的三振次数最多 (TypeID 7)，Bob (PlayerID 2) 的本垒打次数最多 (TypeID 4)，结果如下表所示:

+----------+--------+----------------+
| playerid | typeid | max(playcount) |
+----------+--------+----------------+
|        2 |      4 |             12 |
|        3 |      7 |              9 |
|        3 |     26 |              1 |

到目前为止，我最好的尝试是运行:

SELECT playerid,typeid,MAX(playcount) FROM 
(
SELECT playerid,typeid,COUNT(*) playcount FROM plays GROUP BY playerid,typeid
) AS t GROUP BY typeid;

它返回每种类型的正确最大值，但关联的 PlayerID 都是错误的，我不明白为什么。我确定我遗漏了一些简单的东西(或使它过于复杂)但无法弄清楚。有什么想法吗？

Answer 1

遗憾的是，在 MySQL 中，这个 group=wise max 并不像您希望的那样简单。

这是一种使用类似于 ROW_NUMBER() in MySQL 中建议的方法的方法

SELECT a.*
  FROM (
        SELECT playerid
              ,typeid
              ,COUNT(*) playcount 
          FROM plays 
         GROUP BY playerid,typeid
        ) a
  LEFT JOIN
        (
        SELECT playerid
              ,typeid
              ,COUNT(*) playcount 
          FROM plays 
         GROUP BY playerid,typeid
        ) b
    ON a.typeid = b.typeid
   AND a.playcount < b.playcount
WHERE b.playerid IS NULL