mysql - 在组内以 3 种方式更新排名

Question

在搜索并尝试了很多示例之后，我无法弄清楚这一点。希望你能帮助我。

这是我的表测试:

    +-------+-----------+----------+---------+---------+-------+
    |Id     |   Class   |   Score  |  Rank1  |  Rank2  | Rank3 |
    +-------+-----------+----------+---------+---------+-------+
    |1      |     1     |     9    |    0    |    0    |   0   |
    |2      |     1     |     9    |    0    |    0    |   0   |
    |3      |     1     |     8    |    0    |    0    |   0   |
    |4      |     1     |     7    |    0    |    0    |   0   |
    |5      |     2     |     9    |    0    |    0    |   0   |
    |6      |     2     |     8    |    0    |    0    |   0   |
    |7      |     2     |     8    |    0    |    0    |   0   |
    |8      |     2     |     7    |    0    |    0    |   0   |
    |9      |     2     |     6    |    0    |    0    |   0   |
    +-------+-----------+----------+---------+---------+-------+

我想用 3 种排名更新我的表测试:

1. = 排名 1 = 连续排名的每类分数排名(无双)
2. = 排名 2 = 按连续排名的每类分数排名(双)
3. = 排名 3 = 按每个类(class)的分数排名，没有连续排名(双)

例如:

    +-------+-----------+----------+---------+---------+-------+
    |Id     |   Class   |   Score  |  Rank1  |  Rank2  | Rank3 |
    +-------+-----------+----------+---------+---------+-------+
    |1      |     1     |     9    |    1    |    1    |   1   |
    |2      |     1     |     9    |    2    |    1    |   1   |
    |3      |     1     |     8    |    3    |    2    |   3   |
    |4      |     1     |     7    |    4    |    3    |   4   |
    |5      |     2     |     9    |    1    |    1    |   1   |
    |6      |     2     |     8    |    2    |    2    |   2   |
    |7      |     2     |     8    |    3    |    2    |   2   |
    |8      |     2     |     7    |    4    |    3    |   4   |
    |9      |     2     |     6    |    5    |    4    |   5   |
    +-------+-----------+----------+---------+---------+-------+

注意:它必须适用于 UPDATE 语句。

对于 1. 我已经找到(但不知道如何更新):

SET @prev := null;
SET @cnt := 0;
SELECT IF(@prev <> Class, @cnt := 1, @cnt := @cnt + 1) AS Rank, @prev := Class
FROM Test
ORDER BY Class;

对于 3. 我已经找到(但不知道如何更新):

SELECT  a.Id,
    a.Score,
    a.Class,
    count(b.Score)+1 as Rank
FROM    Test a left join Test b 
    on a.Score>b.Score and a.Class=b.Class 
GROUP BY    a.Id,
        a.Score,
        a.Class;

我添加了基于带小数的值的分数时的结果。奇怪的排名出现了:

SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank3 = 0,@count=0;
UPDATE 1i SET
   Score_pq_raw_rank = (@prev_class := IFNULL(@class,0)),
   Score_pq_raw_rank = (@class := Profile_id),
   Score_pq_raw_rank = (@prev_score := IFNULL(@score,-1)),
   Score_pq_raw_rank = (@score := Score_pq_raw),
   Score_pq_raw_rank = (
   CASE WHEN @prev_class != @class THEN @rank3 := 1
        WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
        WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:=@rank3+1+@count
   END),
   Score_pq_raw_rank = (CASE WHEN @prev_class != @class THEN @count := 0
        WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
        WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
   END),
   Score_pq_raw_rank = @rank3
ORDER BY Profile_id ASC, Score_pq_raw DESC, Rowresult_id ASC;

+--------------+--------------+------------+-------------------+
| Rowresult_id | Score_pq_raw | Profile_id | Score_pq_raw_rank |
+--------------+--------------+------------+-------------------+
|            1 | 3.69054000   |          1 |                 1 |
|            2 | 0.10568000   |          1 |                 2 |
|            3 | -2.08058000  |          1 |                 3 |
|            4 | -2.07316000  |          1 |                 3 |
|            5 | -2.39066000  |          1 |                 3 |
|            6 | -10.23852000 |          2 |                 3 |
|            7 | -8.77718000  |          2 |                 2 |
|            8 | -7.38480000  |          2 |                 1 |
|            9 | -13.49128000 |          2 |                 4 |
|           10 | -19.36774000 |          2 |                 5 |
+--------------+--------------+------------+-------------------+

Answer 1

这是为您提供所需排名的选项。

SELECT Id,Class,Score,
       @prev_class := IFNULL(@class,0),
       @class := Class,
       @prev_score := IFNULL(@score,-1),
       @score := Score,

       CASE WHEN @prev_class != @class THEN @rank1 := 1
            ELSE @rank1 := @rank1 + 1
       END as rank1,
       CASE WHEN @prev_class != @class THEN @rank2 := 1
            WHEN @prev_class = @class AND @prev_score = @score THEN @rank2
            WHEN @prev_class = @class AND @prev_score != @score THEN @rank2:=@rank2+1
       END as rank2,
       CASE WHEN @prev_class != @class THEN @rank3 := 1
            WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
            WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:=@rank3+1+@count
       END as rank3,
       CASE WHEN @prev_class != @class THEN @count := 0
            WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
            WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
       END as count
FROM Test
ORDER BY Class ASC, Score DESC, Id ASC;

这是将完成这项工作的更新，我只是多次使用 SET rank1 因为 mysql 似乎不允许您只设置 @variables 而无需对某些列使用结果...

SET @prev_class = 0,@rank1 = 0,@score = 0,@prev_score = 0,@class =0,@rank2=0,@rank3 =0,@count=0;
UPDATE Test SET
       rank1 = (@prev_class := IFNULL(@class,0)),
       rank1 = (@class := Class),
       rank1 = (@prev_score := IFNULL(@score,-1)),
       rank1 = (@score := Score),
       rank1 = (
       CASE WHEN @prev_class != @class THEN @rank1 := 1
            ELSE @rank1 := @rank1 + 1
       END),
       rank2 = (
       CASE WHEN @prev_class != @class THEN @rank2 := 1
            WHEN @prev_class = @class AND @prev_score = @score THEN @rank2
            WHEN @prev_class = @class AND @prev_score != @score THEN @rank2:=@rank2+1
       END),
       rank3 = (
       CASE WHEN @prev_class != @class THEN @rank3 := 1
            WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
            WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:=@rank3+1+@count
       END),
       rank3 = (CASE WHEN @prev_class != @class THEN @count := 0
            WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
            WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
       END),
       rank3 = @rank3
ORDER BY Class ASC, Score DESC, Id ASC

sqlfiddle

仅更新 rank1

SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank1 = 0;
UPDATE Test SET
       rank1 = (@prev_class := IFNULL(@class,0)),
       rank1 = (@class := Class),
       rank1 = (@prev_score := IFNULL(@score,-1)),
       rank1 = (@score := Score),
       rank1 = (
       CASE WHEN @prev_class != @class THEN @rank1 := 1
            ELSE @rank1 := @rank1 + 1
       END)
ORDER BY Class ASC, Score DESC, Id ASC;

仅更新 rank2

SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank2 = 0;
UPDATE Test SET
       rank2 = (@prev_class := IFNULL(@class,0)),
       rank2 = (@class := Class),
       rank2 = (@prev_score := IFNULL(@score,-1)),
       rank2 = (@score := Score),
       rank2 = (
       CASE WHEN @prev_class != @class THEN @rank2 := 1
            WHEN @prev_class = @class AND @prev_score = @score THEN @rank2
            WHEN @prev_class = @class AND @prev_score != @score THEN @rank2:=@rank2+1
       END)
ORDER BY Class ASC, Score DESC, Id ASC;

仅更新 rank3

SET @prev_class = 0,@class = 0,@prev_score = 0,@score = 0,@rank3 = 0,@count=0;
UPDATE Test SET
       rank3 = (@prev_class := IFNULL(@class,0)),
       rank3 = (@class := Class),
       rank3 = (@prev_score := IFNULL(@score,-1)),
       rank3 = (@score := Score),
       rank3 = (
       CASE WHEN @prev_class != @class THEN @rank3 := 1
            WHEN @prev_class = @class AND @prev_score = @score THEN @rank3
            WHEN @prev_class = @class AND @prev_score != @score THEN @rank3:=@rank3+1+@count
       END),
       rank3 = (CASE WHEN @prev_class != @class THEN @count := 0
            WHEN @prev_class = @class AND @prev_score = @score THEN @count := @count + 1
            WHEN @prev_class = @class AND @prev_score != @score THEN @count := 0
       END),
       rank3 = @rank3
ORDER BY Class ASC, Score DESC, Id ASC;