如何在MySQL中获取超过一个最大记录

Question

这是我想要得到的答案。但如果我在MySQL中使用MAX()函数，它只会返回一条记录。如何处理呢？

A_plus  ID
2     12345
2     45678

如上所述，我使用的SQL语句如下，但它只返回一条记录。

SELECT MAX(A_plus_Num) AS A_plus, ID FROM
(SELECT COUNT(grade) AS A_plus_Num,ID FROM take WHERE grade = 'A+'GROUP BY ID) AS temp


A_plus  ID
2      12345

- DaveyTao

Answer 1

在MySQL中，查询语句会稍微复杂一些。其中一种方法是使用两个带有聚合函数的子查询：

select t.*
from (select t.id, count(*) as A_plus
      from take t
      where t.grade = 'A+'
      group by t.id
     ) t
where t.A_plus = (select max(A_plus)
                  from (select t.id, count(*) as a_plus
                        from take t
                        where t.grade = 'A+'
                        group by t.id
                       )
                 );

- Gordon Linoff