我的代码显示错误的排名值 [英] my code displays wrong ranking values

问题描述

我得到的排名错误，但按降序排列正确值:

I am getting wrong ranking but right values in descending order :

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

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

状态

create table states(state_id int, name_state varchar(50));

INSERT INTO states VALUES
(1, 'CA'),
(2, 'AL'),
(3, 'AZ');

使用下面的代码，我的排名为 6,2,1,3,5 而不是 1,2,3,4,5 为什么?值也是正确的而不是行列.请让我知道我的错误在哪里.非常感谢您的帮助.提前致谢.

using the code below and I am getting rank as 6,2,1,3,5 and not 1,2,3,4,5 why? also the value are correct not the ranks.Please let me know where my mistake is. I really appreciate any help.Thanks in Advance.

select uv.*,
      @curRank := @curRank + 1 AS rank
from  uservotes uv cross join 
     states s 
     on uv.state = s.state_id, (select @curRank := 0) const
where name_state = 'CA'
order by vote desc limit 5;

推荐答案

试试这个.它的工作原理如下 SQLFiddle:http://sqlfiddle.com/#!2/35c6c/33

Try this. It works as shown in this SQLFiddle: http://sqlfiddle.com/#!2/35c6c/33

select agg.*, @curRank := @curRank + 1 AS rank from
(
select uv.*
from uservotes uv cross join 
 states s 
 on uv.state = s.state_id
where name_state = 'CA'
order by vote desc limit 5) as agg, (select @curRank := 0) const;

结果:

ID  NAME    VOTE    STATE   RANK
2   B   80  1   1
1   A   34  1   2
3   bA  30  1   3
4   C   8   1   4
5   D   4   1   5

这篇关于我的代码显示错误的排名值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！