问题描述
您好，世界各地的编码员，
Hi there coders around the world,
我正在开发一个项目，用户可以在其中做某些事情并为此获得积分.为了简化这个问题，假设我们有2个表 user 和 points .
I'm working on a project where users can do certain things and gain points for it. To simplify this question let's say we got 2 tables user and points.
-- table user -- table points
+---------------+ +-----------------------------+
| id | name | | id | points | user_id |
+---------------+ +-----------------------------+
| 1 Tim | | 1 5 1 |
| 2 Tom | | 2 10 1 |
| 3 Marc | | 3 5 1 |
| 4 Tina | | 4 12 2 |
| 5 Lutz | | 5 2 2 |
+---------------+ | 6 7 1 |
| 7 40 3 |
| 8 100 1 |
+-----------------------------+
现在要获取完整的高分列表，我使用以下查询
Now to get the complete highscore-list I use the following query
SELECT u.*, SUM( p.points ) AS sum_points
FROM user u
LEFT JOIN points p ON p.user_id = u.id
GROUP BY u.id
ORDER BY sum_points DESC
从第一个到最后一个所有用户都获得了精美的高分列表
resulting in a fine highscore-list with all users from first to last
+------------------------------+
| id | name | sum_points |
+------------------------------+
| 1 Tim 127 |
| 3 Marc 40 |
| 2 Tom 14 |
| 4 Tina 0 |
| 5 Lutz 0 |
+------------------------------+
好再回到问题本身.在单个用户的个人资料上，我想显示其在高分列表中的排名.
Alright back to the question itself. On the profile of a single user I'd like to show his ranking within the highscore-list.
能否仅使用一个查询来显示例如 Tom(id = 2)排在第3位?
Can this be done using a single query just showing that for example Tom (id=2) is ranked in place 3?
非常感谢:-)
推荐答案
想法是问有多少玩家在@this_user之上排名":
The idea is to ask, "how many players rank above @this_user":
select count(*) + 1 from
(
/* list of all users */
SELECT SUM( p.points ) AS sum_points
FROM user u
LEFT JOIN points p ON p.user_id = u.id
GROUP BY u.id
) x
/* just count the ones with higher sum_points */
where sum_points > (select sum(points) from points where user_id = @this_user)
已编辑，以使结果从1开始而不是从0开始
Edited to make result 1-based instead of 0-based
这篇关于mySQL“高分排名"-查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！