通过分组查找最大值

Question

我有一个users表：

uid country credits
1  1      10
2  1      8
3  2      4
4  2      6

我想找到最大信用按国家分组的用户。输出示例：

[0] => Array(
  'country' => 1,
  'uid'     => 1,
  'credits' => 10
),
[1] => Array(
  'country' => 2,
  'uid'     => 4,
  'credits' => 6
)

我尝试过(不成功)：

SELECT 
   U.*
FROM 
   `users` AS U
WHERE
   U.user_deleted = 0
GROUP BY
   U.country
HAVING
   MAX(U.credits)

我如何解决我的请求？

Answer 1

尝试此查询：

SELECT 
   u.*
FROM 
   `users` AS u
WHERE
   u.credits = (select max(u1.credits) from users u1 where u1.country=u.country  group by u1.country)

Answer 2

试一试

HAVING U.credits = MAX(U.credits)

只是HAVING MAX(u.credits)没有多大意义。这就像是说HAVING 42 -您没有将该值与任何内容进行比较，所以所有行都将被隐式匹配。

Answer 3

SELECT *
  FROM `users`
 WHERE (country,credits)
    IN (
        SELECT country, max(credits)
          FROM users
         GROUP BY country
       )

Test on sqlfiddle.com

或者"hack“，使用get cut of concatenated credits uid并从中删除uid：

SELECT country, 
       substr(max(concat(lpad(credits,10,'0'),uid)),11) as uid,
       max(credits) as credits
  FROM users
 group by country

Test on sqlfiddle.com

最后一个示例通常是最快的，因为它发生在表的一次遍历中，没有子查询。