php - 多次加入后在mysql中查找排名

Question

我试图通过用户获得的总分找到 rank。我确实有这个疑问，但不知道如何继续。

SELECT
wp_grank.ID ,
wp_grank.competitor_ID ,
wp_grank.tournament_ID ,
wp_grank.academy_ID ,
wp_grank.division_ID,
wp_competitor.first_name,
wp_competitor.last_name,
wp_competitor.global_ID,
wp_divisions.gender,
IF(wp_divisions.age_level IN ('Tiny Kids','Kids','Pre Teen','Teen'),CONCAT('(',wp_divisions.gender,')'),'') as filterGender,
wp_tournaments.`name` as tournament_name,
wp_divisions.name as dname,
CONCAT_WS(' >> ', wp_divisions.division_type, wp_divisions.experience_level, wp_divisions.age_level, wp_divisions.weight_class, wp_divisions.gender) as division,
wp_academies.`name` as academy ,
SUM(wp_grank.points) as totalPoints
FROM
`wp_grank`
LEFT JOIN
wp_competitor
ON
wp_grank.competitor_ID = wp_competitor.ID
LEFT JOIN
wp_tournaments
ON
wp_grank.tournament_ID = wp_tournaments.ID
LEFT JOIN
wp_academies
ON
wp_grank.academy_ID = wp_academies.ID
LEFT JOIN
wp_divisions
ON
wp_grank.division_ID = wp_divisions.ID
WHERE
wp_divisions.ID IN (2) AND
YEAR(wp_tournaments.tournament_date)=2015
Group BY
wp_competitor.ID
ORDER BY
SUM(wp_grank.points) DESC ,wp_competitor.`last_name` ASC

Answer 1

我猜您只是想在查询中添加排名列。如果是这样:

set @rank = 0

select (@rank := @rank + 1) as rank, 
       . . .

有时，变量不能很好地与 group by 配合使用，因此您需要一个子查询:

select (@rank := @rank + 1) as rank, q.*
from (<your query here>) q cross join
     (select @rank := 0) params;