Mysql每组最大计数

Question

我有下表:

+---------+--------+-------+
| client  | session| boat  |
+---------+--------+-------+
|       1 |   1943 |     3 |
|       1 |   1943 |     4 |
|       1 |   1944 |     3 |
|       1 |   1944 |     4 |
|       1 |   1945 |     4 |
|       2 |   1944 |     3 |
|       2 |   1944 |     4 |
|       3 |   1944 |     3 |
+---------+--------+-------+

我想获得最大的船数，然后我这样做了:

SELECT boat,session,max(counter) as max_boats FROM (SELECT boat,COUNT(boat) as counter,session FROM `test` GROUP BY boat,session) as cont2 GROUP BY boat

结果:

+------+---------+-----------+
| boat | session | max_boats |
+------+---------+-----------+
|    3 |    1943 |         3 |
|    4 |    1943 |         2 |
+------+---------+-----------+

所以，我的问题是这个查询返回“session = 1943”，这是错误的。正如您在原始表格中看到的那样，3 号船有 3 行，但 session 为 1944，而 4 号船有两行，但也有 session 1944。

问题是，当我执行 max(counter) 时，MySQL 返回第一个 session ，而不是与 max(counter) 对应的行。

也许我的方法是错误的，整个查询都是错误的。

感谢您的回答。

解决方案是:

+------+---------+-----------+
| boat | session | max_boats |
+------+---------+-----------+
|    3 |    1944 |         3 |
|    4 |    1944 |         2 |
+------+---------+-----------+

Answer 1

这是获得此信息的一种方法，这可能不是最好的方法，您也可以查看其他选项。

select 
x.boat, 
x.session, 
max_boats
from (
  select 
  boat, 
  session, 
  count(*) cnt
  from boats
  group by boat, session
)x
join(
  select boat, 
  max(cnt) max_boats
  from(
    select 
    boat, 
    session, 
    count(*) cnt
    from boats
    group by boat, session
  )y
  group by boat
)t on x.boat = t.boat and x.cnt = t.max_boats