mysql - 如何使用SQL获取出现次数最多的组合？

Question

我有一个存储日志条目的表，简化版本是

key value
a   1
a   2
a   3
a   2
b   1
b   2
b   2

我只想提取出现次数最多的组合，例如:

key  value
a    2
b    2

因为a-1和a-3组合只出现一次，而a-2组合出现两次。

欢迎任何类型的 SQL 解决方案，我只是想知道解决这个问题的要点，我会为我自己的平台/sql 变体找到正确的解决方案。

<小时/>

基于 Gordon Linoff 的出色回答和我的平台可以提供的功能，我最终使用了这样的东西:

select 
  key,
  value
from (
  select
    key,
    value,
    rank() over(partition by key order by t1.cnt desc) as rank
  from (
    select
      key,
      value,
      count(*) as cnt
    from 
      table
    group by
      key,
      value
  ) t1
  group by
    key,
    value,
    t1.cnt
) t2
where t2.rank = 1

Answer 1

这在 MySQL 中是一个痛苦，但你可以这样做:

select key, value, count(*) as cnt
from t
group by key, value
having cnt = (select count(*)
              from t
              group by key, value
              order by count(*) desc
              limit 1
             );

如果您使用 MySQL 版本 8 或任何其他数据库，请使用 rank() 或窗口函数:

select key, value, cnt
from (select key, value, count(*) as cnt,
             max(count(*)) over () as max_cnt
      from t
      group by key, value
     ) t
where cnt = max_cnt;