php - 通过分组找到最大值

Question

我有一个用户表:

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)