如何使用ORDER BY在SQL / MySQL中查找行

I have a table user

我有一个表用户

Name     | Poin 
================== 
user1    | 20 
user2    | 30 
user3    | 80 
user4    | 60 
user5    | 10 
user6    | 85 

And I have SQL query

我有SQL查询

SELECT * 
FROM user 
ORDER BY poin

It would appear that the data sequence based on points.

看来数据序列基于点。

But what I need is data like this (for example, I was user1):

但我需要的是这样的数据(例如,我是user1):

Position 1 : user6 - 85 point 
Position 2 : user3 - 80 point 
Position 3 : user4 - 60 point 

You are position 5 : user1 - 20 point

你是第5位:user1 - 20点

UPDATE

I use this sql

我用这个sql

SELECT x.name, x.position
FROM (SELECT t.user,
            @rownum := @rownum + 1 AS position
            FROM user t
           JOIN (SELECT @rownum := 0) r
           ORDER BY t.poin DESC) x
WHERE x.user = 'user1'

SELECT x.name,x.position FROM(SELECT t.user,@rownum:= @rownum + 1 AS位置FROM用户t JOIN(SELECT @rownum:= 0)r ORDER BY t.poin DESC)x WHERE x.user ='user1'

6 个解决方案

#1

This will give current rank for user1:

这将给出user1的当前排名:

SELECT count(*) AS rank
FROM user
WHERE poin >= (SELECT poin FROM user WHERE name = 'user1')

Small issue with this query is that if another user has the same points, it will be assigned the same rank - whether it is correct, it is questionable.

此查询的一个小问题是,如果另一个用户具有相同的点,则会为其分配相同的等级 - 无论是否正确,都是有问题的。

If you want to simply add rank for every user, use this:

如果您只想为每个用户添加排名,请使用以下命令:

SELECT
    @rank:=@rank+1 AS rank,
    name,
    poin
FROM user,
    (SELECT @rank:=0) r
ORDER BY poin DESC

You can use small variation of this query to get rank of single user, but avoid issue of the same ranking ambiguity:

您可以使用此查询的小变体来获得单个用户的排名,但避免出现相同的排名歧义:

SELECT *
FROM (
    SELECT
        @rank:=@rank+1 AS rank,
        name,
        poin
    FROM user,
        (SELECT @rank:=0) r
    ORDER BY poin DESC
) x
WHERE name = 'user1'

#2

select * from user order by poin desc

通过poin desc从用户订单中选择*

Hope this helps

希望这可以帮助

#3

SELECT * FROM user ORDER BY poin DESC

The ORDER BY keyword is used to sort the result-set by a specified column.

ORDER BY关键字用于按指定列对结果集进行排序。

The ORDER BY keyword sorts the records in ascending order by default.

ORDER BY关键字默认按升序对记录进行排序。

If you want to sort the records in a descending order, you can use the DESC keyword.

如果要按降序对记录进行排序,可以使用DESC关键字。

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

#4

SELECT  Name, 
        Poin, 
        @rowNum := @rowNum + 1 AS position
FROM    user
JOIN    (SELECT @rowNum := 0) r
ORDER BY poin;

#5

The following code:

以下代码:

SELECT count(*) AS rank 
FROM user 
WHERE poin >= (SELECT poin FROM user  WHERE name = 'user1')

Has a problem when is has double point in different rows.

在不同的行中有双点时有问题。

#6

SELECT  Name, 
        Poin, 
        @rowNum := @rowNum + 1 AS position
FROM    user
JOIN    (SELECT @rowNum := 0) r
ORDER BY poin DESC;