我有以下查询:
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/