我有以下查询:

SELECT q.category_id as Category_id , COUNT(q.question_id) as count
from questions as q
  INNER JOIN interestingQuestion as i using (question_id)
group by  q.category_id


根据我表中的数据,这给了我以下结果:

Category_id    Count

     5            1
     6            3


现在,我需要找到具有最高计数器的category_id,因此我进行了以下查询:

SELECT t.Category_id, MAX(t.Count)
from(
  SELECT q.category_id as Category_id , COUNT(q.question_id) as count
  from questions as q INNER JOIN interestingQuestion as i using (question_id)
  group by q.category_id
)as t


我得到的结果是:

category_id    MAX(t.count)
    5              3


这是一个混淆的结果,它正在找到最大计数器,但它给了我一个错误的category_id

为什么会这样呢?以及我该如何解决?

最佳答案

您可以使用此:

SELECT
  q.category_id as Category_id,
  COUNT(q.question_id) as count
FROM
  questions as q INNER JOIN interestingQuestion as i
  USING (question_id)
GROUP BY q.category_id
ORDER BY
  COUNT(q.question_id) DESC
LIMIT 1


这将按COUNT递减顺序对结果进行排序,仅返回包含所需值的第一行。

编辑

如果有多个具有相同最大值的行,则可以使用以下内容:

SELECT
  q.category_id as Category_id,
  COUNT(q.question_id) as count
FROM
  questions as q INNER JOIN interestingQuestion as i
  USING (question_id)
GROUP BY
  q.category_id
HAVING
  COUNT(q.question_id) = (SELECT MAX(t.Count) FROM (
    SELECT COUNT(q.question_id) as count
    FROM
      questions as q INNER JOIN interestingQuestion as i
      USING (question_id)
    GROUP BY
      q.category_id) as t)


我将您的查询用作子查询来计算最大计数,并且返回所有行HAVING COUNT()=(您的查询返回的最大值)。

关于mysql - 带有最大数据的sql max函数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16385767/

10-11 03:27