mysql - 从子查询中选择具有最大值的行，而不使用 LIMIT

Question

假设我有一张表films(film, category)。我想找到电影最多的类别。我该如何不使用 LIMIT？

我想我可以做这样的事情:

SELECT category
FROM
(SELECT category, COUNT(*) AS num
FROM films
GROUP BY category) AS T1
WHERE num =
(SELECT MAX(num)
FROM
(SELECT category, COUNT(*) AS num
FROM films
GROUP BY category) AS T2) 

但是有没有更优雅的方法呢？最好是我不必多次编写相同的子查询？

谢谢!

(如果你想知道为什么我不能使用 LIMIT，那是作业)

Answer 1

您可以使用变量来创建行号:

SELECT category
FROM
    (
       SELECT
          category
          COUNT(*) as NumOfFiles
          ,(@rn:= @rn + 1) as RowNumber
       FROM
          Films f
          CROSS JOIN (SELECT @rn:=0) vars
       GROUP BY
          category
       ORDER BY
          COUNT(*) DESC
    ) t
WHERE
    t.RowNumber = 1