mySQL“高分排名"-查询 [英] mySQL "Rank in Highscore"-Query

问题描述

您好，世界各地的编码员，

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“高分排名"-查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！