试试这个

SELECT m.rank,m.scorer AS points,m.player_id, m.quiz_id 
FROM (
    SELECT d.player_id,d.quiz_id, d.scorer,  
    @cur:= IF(quiz_id=@id, @cur+1, 1) AS rank, 
    @id := quiz_id 
    FROM (
            SELECT t.player_id, quiz.id as quiz_id, SUM(t.score) as scorer
            FROM answers t JOIN profile ON profile.player_id = t.player_id
                    JOIN quiz ON t.quiz_id = quize.id  
            WHERE t.is_active = 1 AND quiz.contest_id = 1
                    AND profile.signin_source_id != 1 AND profile.is_active = 1
                    AND t.quiz_id IN (1,2) 
            GROUP BY t.player_id, quiz.id 
            ORDER BY scorer DESC
    ) d, (SELECT @id:=(SELECT MIN(id) FROM quiz), @cur:=0) AS init
    order by d.quiz_id, d.scorer desc) m 
WHERE m.scorer > 0

尝试此查询，它更简单：

select @quiz_id_lag := 0, @rank := 1;

select rank, points, player_id, quiz_id from (
  select points,
         player_id,
         case when @quiz_id_lag = quiz_id then @rank := @rank + 1 else @rank := 1 end rank,
         @quiz_id_lag,
         @quiz_id_lag := quiz_id,
         quiz_id
  from tbl
  order by quiz_id, points desc
) a;

要将其合并到查询中，请尝试：

SELECT @quiz_id_lag := 0, @rank := 1;

SELECT rank,
       scorer AS points,
       player_id
FROM (
    SELECT quiz_id,
           player_id,
           scorer,
           CASE WHEN @quiz_id_lag = quiz_id THEN @rank := @rank + 1 ELSE @rank := 1 END rank,
           @quiz_id_lag := quiz_id,
           quiz_id
    FROM ( 
         SELECT t.player_id,
                SUM(t.score) AS scorer,
                t.quiz_id
         FROM answers t
         JOIN PROFILE ON profile.player_id = t.player_id
         JOIN quiz ON t.quiz_id = quiz.id
         WHERE t.is_active = 1 AND quiz.contest_id = 1 AND profile.signin_source_id != 1
           AND profile.is_active = 1 AND t.quiz_id IN (1, 2)
        GROUP BY t.player_id
    ) d
    WHERE scorer > 0
    ORDER BY quiz_id, scorer DESC
) m

最终得到了所需的结果，以查询结束，以便为级别获得适当的排名：

SELECT m.rank,m.scorer AS points,m.player_id, m.quiz_id  FROM (
 SELECT d.player_id,d.scorer, 
    @rownum:= CASE WHEN @quiz_id <> d.quiz_id THEN 1 ELSE @rownum+1 END as rank, 
    @quiz_id:= d.quiz_id as quiz_id FROM 
    (SELECT @rownum := 1) r,
    (SELECT @quiz_id := 0) c,(
    SELECT t.player_id,SUM(t.score) as scorer, t.quiz_id 
    FROM answers t JOIN profile ON 
    profile.player_id = t.player_id
    JOIN quiz ON t.quiz_id = quiz.id
    WHERE t.is_active = 1 AND quiz.contest_id = 2 AND 
     profile.signin_source_id != 1 AND profile.is_active = 1
     GROUP BY t.player_id,t.quiz_id 
     ORDER BY quiz_id DESC,scorer DESC, t.created_utc ASC) d
   ) m 
WHERE m.scorer > 0 ORDER BY quiz_id

这将给出特定组的所有级别的整个结果集，如果要从特定组中获取特定级别的排名，请执行添加

AND t.quiz_id IN (1,2)

感谢所有曾经参与的人！