MySQL 选择列值的最大计数

Question

我有一个名为 Euro_Cup 的 table 。

[在此处输入图像描述][1]

Group   Country Rank    Jersey  Position    Age Selections  Club    Player
A   Brazil  3   18  Midfielder  29  24  Internazionale      Hernanes
A   Cameroon    56  18  Midfielder  28  38  Antalyaspor     Eyong Enoh
A   Croatia 18  18  Forward 34  92  VfL Wolfsburg   Ivica Olic
A   Mexico  20  18  Defender    27  104 Bayer Leverkusen    Andres Guardado
B   Australia   62  18  Goalie  32  8   Adelaide United     Eugene Galekovic
B   Chile   14  18  Defender    28  65  Nottingham Forest   Gonzalo Jara
B   Spain   1   18  Defender    25  26  Barcelona       Jordi Alba
B   Netherlands 15  18  Midfielder  24  6   Norwich City    Leroy Fer

我必须编写一个 SQL 查询来按球员的 Jersey 号码对他们进行分组，然后对于每个 Jersey 号码组，返回该 Jersey 号码最常见的位置。

例如，18 号 Jersey 有 3 名中卫、8 名后卫和 2 名守门员，则结果应为 18 号 Jersey 、18 号后卫(bcos Jersey 号码为 18 号的最大球员)

我已经尝试过

 select jersey,position,count(position) as cnt 
 from euro_cup2
 group by jersey,position 
 having count(position) in 
                       (select max(cnt) from   (select jersey,position,count(position) as cnt 
                      from euro_cup2
                       group by jersey,position)a)

但是它不起作用。任何帮助将不胜感激

Answer 1

我查看了您的所有评论，这将为您提供搜索内容:

SELECT
euro_cup2.jersey,
a.position,
MAX(cnt) as count
FROM euro_cup2
LEFT JOIN
(SELECT
   jersey,
   position,
   (COUNT(position)) AS cnt 
   FROM euro_cup2
   GROUP BY jersey,position) AS a ON euro_cup2.jersey=a.jersey
GROUP BY jersey;

结果如下:

问候