mysql - 如何使用内部选择获取 max(count(*)) 的行

Question

我有一个查询，它获取按三个字段分组的内部选择的最大计数。现在我不仅想要数字，还想要整行。我只是想不通。这是返回数字的查询:

SELECT MAX (count) FROM
(SELECT COUNT (*) AS count,
        field1,
        field2,
        field3   
    FROM someScheme.someTable
    GROUP BY field1, field2, field3
     ) AS counts

Answer 1

使用order by和limit:

SELECT COUNT(*) AS count, field1, field2, field3   
FROM someScheme.someTable
GROUP BY field1, field2, field3
ORDER BY COUNT(*) DESC
LIMIT 1;

注意:这只会返回最大值的一个示例。

如果你想要所有可能的行，那就更复杂了:

SELECT COUNT(*) AS count, field1, field2, field3   
FROM someScheme.someTable
GROUP BY field1, field2, field3
HAVING COUNT(*) = (SELECT MAX(cnt)
                   FROM (SELECT COUNT(*) AS cnt 
                         FROM someScheme.someTable
                         GROUP BY field1, field2, field3
                        ) t
                  );