mysql - mysql查询如何才能达到显示的结果？

我有两张这样的桌子：

Table group         Table user
+----+-----------+  +----+----------+------+----------+
| id | groupname |  | id | username | rank | group_id |
+----+-----------+  +----+----------+------+----------+
|  1 | Friends   |  |  1 | Frank    |    1 |        1 |
|  2 | Family    |  |  2 | Mike     |    3 |        1 |
+----+-----------+  |  3 | Steve    |    2 |        1 |
                    |  4 | Tom      |    1 |        2 |
                    +----+----------+------+----------+

我想选择所有组，得到每个组的最高排名（最高数字）的用户。所以基本上我想得到这个结果：

+-----------------+----------+---------+---------------+
| group.groupname | group.id | user.id | user.username |
+-----------------+----------+---------+---------------+
| Friends         |        1 |       2 |         Mike  |
| Family          |        2 |       4 |          Tom  |
+-----------------+----------+---------+---------------+

你的选择怎么样？
也许很简单，但我现在还不明白。。。。

最佳答案

select g.groupname, u.group_id, u.id as user_id, u.username
from group g
inner join (
    select group_id, max(rank) as MaxRank
    from user
    group by group_id
) um on g.id = um.group_id
inner join user u on um.group_id = u.group_id and um.MaxRank = u.rank

关于mysql - mysql查询如何才能达到显示的结果？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2117903/