mySQL查询：按组点的顺序按连接成员点的总和排序
问题描述：
I got 2 tables which are linked. GROUPS can have multiple USERS and USERS can be in multiple GROUPS.
GROUPS
|id| name |
----------------
|1| Koalas |
|2| Grizzlies |
|3| Hornets |
----------------
USERS
|id| firstName | points |
----------------
|1| Bob | 2
|2| Hans | 4
|3| Jerome | 1
|4| Katy | 6
----------------
GROUP_USER
|id| group_id | user_id |
--------------------------
|1| 1 | 2
|2| 1 | 4
|3| 2 | 1
|4| 2 | 2
|5| 3 | 3
|6| 3 | 4
----------------
Now I want to rank the groups by the points of their members. Result:
GROUP_USER
|rank| group_name | user_points |
-----------------------------------
|1 | Koalas | 10
|2 | Hornets | 7
|3 | Grizzlies | 6
Don´t really know how to start.
我有两个链接的表格。 GROUPS可以有多个USERS，USERS可以在多个组中。 p>
群组的 | ID | name | ---------------- | 1 | 考拉| | 2 | 灰熊队| | 3 | 黄蜂队| ---------------- USERS | id | firstName | 点| ---------------- | 1 | 鲍勃| 2 \ N | 2 | 汉斯| 4 | 3 | 杰罗姆| 1 | 4 | 凯蒂| 6 ---------------- GROUP_USER \ N | ID | group_id | user_id | -------------------------- | 1 | 1 | 2 \ N | 2 | 1 | 4 | 3 | 2 | 1 | 4 | 2 | 2 | 5 | 3 | 3 | 6 | 3 | 4 ---------------- code> pre>现在我想按组成员的分数对组进行排名。 Result： p>
GROUP_USER \ N |秩| group_name | user_points | ----------------------------------- | 1 | 考拉| 10 | 2 | 黄蜂队| 7 | 3 | 灰熊队| 6 code> pre>不知道如何开始。 p> div>
答
select @rank := @rank + 1 as rank, name, user_points
from
(
select g.name,
sum(u.points) as user_points
from groups g
left join group_user gu on gu.group_id = g.id
left join users u on gu.user_id = u.id
group by g.name
order by user_points desc
) tmp
cross join (select @rank := 0) r