MySQL -如何获取行号

Question

我有一张这样的桌子：

tb_bid_test
+-------------+---------------------+
| Field       | Type                |
+-------------+---------------------+
| username    | varchar(20)         |
| poin        | bigint(20)          |
+-------------+---------------------+

然后我运行这个查询：

select @r := @r+1 as rank, z.* from(SELECT username, sum(poin) as jumlahPoin FROM `tb_bid_test` GROUP BY username order by jumlahPoin desc limit 3 )z, (select @r:= 0)y;

结果是：

+--------+------------+--------------+
| rank   | username   |  jumlahPoin  |
+--------+------------+--------------+
|   1    |        neo |      500     |
|   2    |     andhie |      348     |
|   3    |       john |      123     |
+--------+------------+--------------+

我想得到一行用户名和this，如下所示：

+--------+------------+--------------+
| rank   | username   |  jumlahPoin  |
+--------+------------+--------------+
|   2    |      andhie|      348     |
+--------+------------+--------------+

或者其他人的用户名，并获得排名，如果我使用之前的代码，并添加where username ='andhie'，排名始终是1。如何获得当前排名的基础上，他们的积分？

Answer 1

使用值'andhie‘从结果用户名中选择

create table tb_bid_test (username varchar(20),poin bigint(20));
insert into tb_bid_test values ('neo',500);
insert into tb_bid_test values ('andhie',348);
insert into tb_bid_test values ('john',123);


select * from (
select 
@r := @r+1 as rank, 
z.* 
from(SELECT username, sum(poin) as jumlahPoin FROM `tb_bid_test` 
GROUP BY username order by jumlahPoin desc limit 3 )z, 
(select @r:= 0)y 
  ) as t where username ='andhie'

Try the fiddle here

Answer 2

尝试此查询：

Select * from (select @r := @r+1 as rank, z.* from(SELECT username, sum(poin) as jumlahPoin FROM `tb_bid_test` GROUP BY username order by jumlahPoin desc limit 3 )z, (select @r:= 0)y;) where username="andhie"

Answer 3

使用嵌套查询

SELECT *
FROM (
        SELECT @r := @r + 1 AS rank, z.*
        FROM (
                SELECT username, SUM(poin) AS jumlahPoin
                FROM tb_bid_test
                GROUP BY username
                ORDER BY jumlahPoin 
                DESC limit 3
        ) z,
        (SELECT @r := 0) y
    ) tbl
WHERE username = "andhie"