如何仅使用mysql查询获取特定行的位置排名? [英] how to get position rank of specific row using just mysql query?

问题描述

我有包含列的用户表:id、name、points、extra_points.当排名是积分和extra_points的总和时，如何获得特定用户的排名?

I have table Users which contains columns: id, name, points, extra_points. How can I get position rank for specific user, when the rank is sum of points and extra_points?

对于使用此查询的所有用户列表排名:"SELECT id, (points + extra_points) AS total FROM users ORDER BY total desc"; 和带有 html ol<的 while 循环/code> 标签(所以有位置编号).但我不知道如何显示单个用户的排名(在用户个人资料页面中)?任何帮助都会很棒.

For all users list rank Im using this query: "SELECT id, (points + extra_points) AS total FROM users ORDER BY total desc"; and the while loop with html ol tag (so there is position number). But I dont know how to show the rank for a sinlge user (in user profile page)? Any help will be great.

推荐答案

SELECT users1.id, (users1.points+users1.extra_points) AS total, COUNT(*)+1 AS rank
FROM users users1
INNER JOIN users users2 ON users1.points+users1.extra_points < users2.points+users2.extra_points
WHERE users1.id = $id

这篇关于如何仅使用mysql查询获取特定行的位置排名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！