MYSQL排序按W /计数 [英] MYSQL Order By W/Count
问题描述
我的表有用户名和用户点,我正在处理领导板列表。我想根据积分得到前十名玩家。我已将此查询表达为:
My table has usernames and points of user, and I am working on a leader boards list. I would like to get the top ten players based on points. I have formulated this query as:
SELECT
users.username,
users.points
FROM users
ORDER BY users.points DESC
LIMIT 10
我也想得到玩家站在根据1号播放器,而不创建一个新的列。是否有MYSQL查询,通过点获得 ORDER BY DESC
,以及从 COUNT
> ORDER 找到列表中的用户? ( WHERE username =
)。
However, I would also like to get where the player stands in accordance to the number 1 player, without creating a new column for it. Is there a MYSQL query to get the ORDER BY DESC
by points as well as COUNT
from that ORDER
to find the user in the list? (WHERE username=
).
编辑澄清:以上语句。 I.E. 1 - user1 - 1000点,2 - user2 - 750点..等等... N - currentPlayer - currentPoints。我不介意使用 JOIN
语句,但我不希望有一个新的玩家列按排名排序。
Edit for clarification: I would like the count of the users from desc by the above statement. I.E. 1 - user1 - 1000 points, 2 - user2 - 750 points.. etc etc... N - currentPlayer - currentPoints. I don't mind using a JOIN
statement, but I don't want to have a new column of players sorted by ranking.
推荐答案
尝试:
SELECT * FROM (
select @rownum:=@rownum+1 `rank`, u.username, u.points
from users u, (SELECT @rownum:=0) r
order by points desc) AS X
WHERE username ="Bob";
示例数据:
CREATE TABLE users(
id int auto_increment primary key,
username varchar (30),
points INT
);
INSERT INTO users(username,points)
VALUES ('Bob', 1000), ('Jack',750), ('Joe', 500)
这篇关于MYSQL排序按W /计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!