假设我有下表:
user_id | category_id | points
-------------------------------
1 | 1 | 4
2 | 1 | 2
2 | 1 | 5
1 | 2 | 3
2 | 2 | 2
1 | 3 | 1
2 | 3 | 4
1 | 3 | 8
有人可以帮我构造一个查询来返回每个类别的用户排名-像这样:
user_id | category_id | total_points | rank
-------------------------------------------
1 | 1 | 4 | 2
1 | 2 | 3 | 1
1 | 3 | 9 | 1
2 | 1 | 7 | 1
2 | 2 | 2 | 2
2 | 3 | 4 | 2
首先,您需要获取每个类别的总积分。然后,您需要枚举它们。在MySQL中,最容易使用变量完成此操作:
SELECT user_id, category_id, points,
(@rn := if(@cat = category_id, @rn + 1,
if(@cat := category_id, 1, 1)
)
) as rank
FROM (SELECT u.user_id, u.category_id, SUM(u.points) as points
FROM users u
GROUP BY u.user_id, u.category_id
) g cross join
(SELEct @user := -1, @cat := -1, @rn := 0) vars
ORDER BY category_id, points desc;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句