How to achieve ranking in mysql

How to achieve ranking in mysql: use demo function, the syntax is [SELECT banji,avg(score) as AvgS FROM table_test GROUP BY banji ORDER BY AvgS DESC].

How to achieve ranking in mysql:

can be implemented with Demo

Pay attention to the A in it , it is a nested query, so the ranking will be correct.

FROM
(
     SELECT A.*,@rank:=@rank+1 as pm
     FROM 
     (
      SELECT banji,avg(score) as AvgS FROM table_test GROUP BY banji  ORDER BY AvgS   DESC
     ) A ,(SELECT @rank:=0) B
) M
ORDER BY M.banji

If there is no subquery in it and the following SQL is used, the sorting will be wrong. What goes wrong depends on whether there is more than one grouping in GROUP BY.

SELECT banji,avg(score) as AvgS ,@rank:=@rank+1 as pm
FROM table_test A,(SELECT @rank:=0) B
GROUP BY banji
ORDER BY AvgS   DESC

Reason: @rank ranking occurs before GROUP BY. GROUP BY groups the ranked results. If you want to rank grouped results, use a subquery.

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of How to achieve ranking in mysql. For more information, please follow other related articles on the PHP Chinese website!