Mysql Group By Levels排名

Question

rank    points  player_id   quiz_id
 1       88        1          40
 2       80        3          40
 3       30        3          41
 4       20        1          41

从以下查询中获取此输出:

SELECT m.rank,
       m.scorer AS points,
       m.player_id
FROM
  ( SELECT d.player_id,
           d.scorer, @rownum := @rownum + 1 AS rank
   FROM
     ( SELECT t.player_id,
              SUM(t.score) AS scorer
      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
      ORDER BY scorer DESC, t.created_utc ASC) d,

     (SELECT @rownum := 0) r) m
WHERE m.scorer > 0

但是，我想要的输出是将每个级别分开的排名。

rank    points  player_id   quiz_id
 1       88        1          40
 2       80        3          40
 1       30        3          41
 2       20        1          41

我关注了这些:

How to perform grouped ranking in MySQL

https://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

却得不到想要的输出。任何建议或帮助表示赞赏。

Answer 1

试试这个查询，IMO 更简单:

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