Mysql Max与按查询分组 [英] Mysql Max with Group by query

问题描述

我有一个可能是经典"的mysql max/group by问题 这是我的基本表结构.

I've got a possibly 'classic' mysql max/group by question Here is my basic table structure.

id | userid | username | date | score
1 | 1111 | joe | 2012-05-16 | 1000
2 | 2222 | john | 2012-03-17 | 2000
3 | 3333 | jill | 2012-02-11 | 3000
4 | 2222 | john | 2012-12-25 | 5000
5 | 3333 | jill | 2012-04-08 | 4000
6 | 1111 | joe | 2012-06-17 | 1000
7 | 1111 | joe | 2012-01-14 | 500
8 | 2222 | john | 2012-06-29 | 6000
9 | 4444 | bill | 2012-08-08 | 4000

我想获得每个用户的最高分数. 每个用户的结果中只会显示一个分数.

I would like to get each user's highest score. Each user should have only one score displayed in the result.

My desired result is
id | userid | username | date | score
8 | 2222 | john | 2012-06-29 | 6000
9 | 4444 | bill | 2012-08-08 | 4000
5 | 3333 | jill | 2012-04-08 | 4000
6 | 1111 | joe | 2012-06-17 | 1000

如果用户的最高得分出现多次(例如ID 1和6)，则应显示最近的日期.

If the user's highest score appears more than once (ex id 1&6), the most recent date should be displayed.

这是我当前的查询

select s.*
from scores as s
inner join (
    select userid, max(score) as max_score
    from scores
    group by userid
    ) t on s.userid = t.userid and s.score = t.max_score
order by score DESC, date DESC

哪个返回不正确的结果

id | userid | username | date | score
8 | 2222 | john | 2012-06-29 | 6000
9 | 4444 | bill | 2012-08-08 | 4000
5 | 3333 | jill | 2012-04-08 | 4000
6 | 1111 | joe | 2012-06-17 | 1000
1 | 1111 | joe | 2012-05-16 | 1000

关于如何解决此问题的任何建议?

Any suggestions on how to resolve this?

我还应该提到，用户名和用户名之间的关系不是一对一的.

I should also mention that the relationship between userid and username is not one to one.

推荐答案

这应该为您提供所需的结果.由于MySql没有作为SQL SERVER的PARTITION函数，因此我使用了已知的解决方法.

This should give you the result you are looking for. Since MySql doesn't have a PARTITION function as SQL SERVER, I used a known work around.

SELECT tmp.id, tmp.userid,tmp.username,tmp.`date`,score FROM
   (SELECT *, IF(@prev<>userid, @rownum := 1, @rownum := @rownum+1 ) AS rank,
           @prev := userid
    FROM scores s0
    JOIN (SELECT @rownum := NULL, @prev := 0) AS r
    ORDER BY s0.userid, s0.score DESC, s0.`date` DESC
   ) AS tmp
WHERE tmp.rank=1
ORDER BY tmp.score DESC, tmp.`date` desc

这篇关于Mysql Max与按查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！