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

我试图根据评级百分比获得排名，因此像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

但它返回错误的耙索引



查询出了什么问题？

最佳答案

使用变量进行排名通常会在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;

关于mysql - 在mysql上获得排名会产生错误的排名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47826021/