mysql - 获取用户在 mySql 数据库中的位置

Question

我正在尝试获取特定用户在篮球得分排行榜中的位置。数据库中只有一张表。这是mySQL中数据库的图像

我想它会是这样的:

SELECT userID, SUM(points) as '`basketballScore`'

FROM user 

ORDER BY SUM(points) DESC

Answer 1

您需要一个group by子句:

select userID,
    SUM(score) as basketballScore
from user
group by userID
order by basketballScore desc

另请注意，我在 order by 子句中重复使用了别名 basketballScore。

如果需要创建排名，可以使用用户变量:

set @rank := 0;

select @rank := @rank + 1 as rank, 
    userID,
    SUM(score) as basketballScore
from user
group by userID
order by basketballScore desc