mysql 组内最大计数 [英] mysql max count within groups

问题描述

我有一个表格如下

id  qNum  opNum
1   1     3
2   1     3
3   1     2
4   1     1
5   2     4
6   2     4
7   2     4
8   2     1

我需要得到按 qNum 和 opNum 分组的计数，这很好，但我只想返回每个分组中具有最大计数的记录的 qNum 和 opNum

I need to get the count grouped by qNum and then opNum, and this is fine to do, but I only want to return the qNum and opNum of the records with the max count in each grouping

所以我理想情况下需要一个看起来像这样的记录集

So I ideally need a recordset that looks like

qNum  opNum  maxCount
1     3      2
2     4      3

非常感谢您的帮助！

推荐答案

更简单的解决方案可能是:

Simpler solution might be:

SELECT qNum, opNum, MAX(temp.maxcount) AS maxcount FROM(
SELECT qNum, opNum, COUNT(*) AS maxCount
FROM t
GROUP BY opNum, qNum ORDER BY maxcount DESC
) AS temp GROUP BY qNum

这篇关于mysql 组内最大计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！