Я пытаюсь получить ранжирование на основе процентного рейтинга, так что 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
order by
и даже order by
в самых последних версиях MySQL. Итак, используйте подзапрос:
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;
1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from rating r inner join rateelement r1 on r.elementid =' at line 3 SQL6.sql 2 1