php - 多次加入后在mysql中查找等级

我试图通过用户获得的总积分来找到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

最佳答案

你可以试试这个：

SET
    @prev = 0,
    @curr = 0,
    @rank = 1,
    @i = 1;

SELECT
    @prev := @curr,
    @rank := IF(
        @prev = @curr,
        @rank,
        @rank + @i
    ) AS rank,
    IF(
        @prev != SUM(wp_grank.points),
        @i := 1,
        @i := @i + 1
    ) AS counter,
    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,
    @curr := 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
    `totalPoints` DESC,
    wp_competitor.`last_name` ASC;

注：
对于您需要的tied记录的排名，我使用session variable定义排名是基于前一个值increase还是retain。
干杯

关于php - 多次加入后在mysql中查找等级，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36465024/