在mysql中使用where子句按降序获取此人的排名

Question

我已经试过了http://sqlfiddle.com/#!2/df2ec/1获取数据：我正在尝试获取名称A的排名，它是4，但我无法做到这一点。

SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t ORDER BY vote DESC

在数据表中：

create table uservotes(id int, name varchar(50), vote int);

INSERT INTO uservotes VALUES
(1, 'A', 34),
(2, 'B', 80),
(3, 'bA', 30),
(4, 'C', 8),
(5, 'D', 4),
(6, 'E', 14),
(7, 'F', 304),
(8, 'AA', 42),
(9, 'Ab', 6),
(10, 'Aa', 10);

我无法得到在哪里从句。基本上我想知道哪个等级是基于投票的名字。我尝试了：

SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes WHERE name= A , (SELECT @rownum := 0) t ORDER BY vote DESC

但我犯了个错误。我真的很感谢你的帮助。提前谢谢。

错误：

Answer 1

SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t where name='A' ORDER BY vote DESC

为了获得军衔：

select rank, name, vote
from (SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t ORDER BY vote DESC) z
where name='A';

Answer 2

select a.name, (select count(*) + 1 from uservotes b where (a.vote > b.vote)) as rank 
from uservotes a having name="A" order by rank

Answer 3

1） 试试这个：

SELECT 
    @i:=@i+1 AS rn, 
    t.*
FROM 
    uservotes AS t,
    (SELECT @i:=0) AS tbl
    ORDER BY vote desc

2）然后得到A的特定行很容易：

select * From
(
SELECT 
    @i:=@i+1 AS rn, 
    t.*
FROM 
    uservotes AS t,
    (SELECT @i:=0) AS tbl
ORDER BY vote desc

) x where x.name = 'A'