有两行的最高值为5,但下面的查询仅返回最后一个最高记录...我如何才能获得所有具有最高成员值的行?

我可以使用max子句plz帮助吗?

SELECT teamcode,MAX(member) AS members FROM(
    SELECT t.`teamcode` AS teamcode,
    COUNT(*) AS member
    FROM `users` u
    JOIN teams t
    ON u.teamcode=t.tm_id
    GROUP BY `teamcode`
) AS maxcount


如何获得所有最高价值?

最佳答案

首先,由于您只想使用团队代码进行计数:您无需使用JOIN
但是,您确实需要一个辅助表。

  SELECT teamcode,
         COUNT(*) as members
    FROM users
GROUP BY teamcode
  HAVING COUNT(*) = (   SELECT COUNT(*)
                          FROM users
                      GROUP BY teamcode
                      ORDER BY COUNT(*) DESC
                         LIMIT 1             );


SQL-Fiddle(如果需要)。

使用的参考:
SQL Query of Using Functions - How to get MAXIMUM Count of the list

10-08 01:39