代码之家 › 专栏 › 技术社区 › acme

MySQL查询如何才能获得显示的结果?

aggregate group-by mysql

acme · 技术社区 · 16 年前

我有两张这样的桌子:

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  |
+-----------------+----------+---------+---------------+

选择是怎样的? 这也许很简单,但我现在还不明白……

2 回复 | 直到 16 年前

D'Arcy Rittich 16 年前

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

Dan Soap 16 年前

编辑2:

我以前的回答是错误的, max() 调用破坏了结果。以下是正确的解决方案:

SELECT g.groupname, g.id AS group_id, u.id AS user_id, u.username
FROM `user` u
LEFT JOIN `group` g ON (u.group_id=g.id)
WHERE u.rank=(
    SELECT MAX(rank) 
    FROM `user` u2 
    WHERE u.group_id=u2.group_id
)

办理登机手续 WHERE 条款也应该更容易理解。

mysql> SELECT g.groupname, g.id AS group_id, u.id AS user_id, u.username
    -> FROM `user` u
    -> LEFT JOIN `group` g ON (u.group_id=g.id)
    -> WHERE u.rank=(
    ->     SELECT MAX(rank)
    ->     FROM `user` u2
    ->     WHERE u.group_id=u2.group_id
    -> );
+-----------+----------+---------+----------+
| groupname | group_id | user_id | username |
+-----------+----------+---------+----------+
| Friends   |        1 |       2 | Mike     |
| Family    |        2 |       4 | Tom      |
+-----------+----------+---------+----------+
2 rows in set (0.00 sec)

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前