mysql - 在 mysql 上获取排名产生错误的排名

Question

我试图根据评级百分比获得排名，因此 mysql 查询如下

select c.id , sum((r.value * 20))/ count(r1.pagetypeid)  as score, @curRank := @curRank + 1 AS rank from (SELECT @curRank := 0) cr, rating as r 
inner join rateelement as r1 on r.elementid = r1.id
inner join ratesubscription as r2 on r.subscriptionid = r2.id
inner join consultant as c on r2.consultantid = c.id
where r1.displayorder not in (6) and r2.agencyid = 38
group by  c.id order by score desc

但它返回错误的排名索引

查询出了什么问题？

Answer 1

使用变量进行排名通常会出现 group by 问题，甚至在最新版本的 MySQL 中也会出现 order by 问题。因此，使用子查询:

select x.*, (@curRank := @curRank + 1) AS rank
from (select c.id, sum((r.value * 20))/ count(r1.pagetypeid) as score 
      from rating r inner join
           rateelement r1
           on r.elementid = r1.id inner join
           ratesubscription r2
           on r.subscriptionid = r2.id inner join
           consultant c
           on r2.consultantid = c.id
      where r1.displayorder not in (6) and r2.agencyid = 38
      group by c.id
      order by score desc
     ) x cross join
     (SELECT @curRank := 0) cr;