php - MySQL查询: order by group points by sum of join member points

Question

我有两个链接的表。组可以有多个用户，用户也可以属于多个组。

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

现在我想根据成员的分数对群组进行排名。结果:

GROUP_USER
|rank| group_name  | user_points  |
-----------------------------------
|1   |  Koalas     |  10
|2   |  Hornets    |  7 
|3   |  Grizzlies  |  6

真的不知道如何开始。

Answer 1

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