mysql - 在 MYSQL 中排名并列结果

Question

我有一个看起来像这样的 MYSQL 表:

ID | NAME CODE | SCORE
1: A01: 1
2: A01: 4
3: A01: 5
4: A02: 2
5: A02: 3
6: A02: 3
7: A02: 7

我正在寻找的排名结果是这样的(考虑到 2 个不同的 NameCode 组):

ID | NAME CODE | SCORE | RANK

1: A01: 1: 1
2: A01: 4: 2
3: A01: 5: 3
4: A02: 2: 1
5: A02: 3: 2
6: A02: 3: 2
7: A02: 7: 4

我可以使用以下代码对分数进行排名:

SELECT
  my_table.id,
  my_table.NameCode,
  my_table.Score,
  @prev := @curr,
  @curr := Score,
  @rank := IF(@prev = @curr, @rank, @rank + @i) AS rank,
  IF(@prev <> Score, @i:=1, @i:=@i+1) AS counter
FROM
 my_table,
  (SELECT @curr := null, @prev := null, @rank := 1, @i := 0  
  ) tmp_tbl 

订购方式 my_table.Score ASC

然而，这并没有根据每个 NameCode 组进行排名。结果如下:

ID | NAME CODE | SCORE | RANK

1: A01: 1: 1
2: A01: 4: 5
3: A01: 5: 6
4: A02: 2: 2
5: A02: 3: 3
6: A02: 3: 3
7: A02: 7: 7

有谁知道我在每个 NameCode 组中排名的最有效方式？谢谢。

Answer 1

基本上你希望你的逻辑说如果它们都是相同的重复排名，如果名称相同则增加排名或者将其重置回 1

SELECT 
    id, 
    name, 
    score,
    @rank := if(
        @name = name and @score = score, 
        @rank, 
        if(@name = name, @rank + 1, 1)
    ),
    @name := name, @score:= score
FROM your_table
CROSS JOIN (SELECT @rank := 1, @name := '', @score := 0) t;

FIDDLE

编辑:如果您需要对数据进行排序，就这样做

SELECT 
    id, 
    name, 
    score,
    @rank := if(
        @name = name and @score = score, 
        @rank, 
        if(@name = name, @rank + 1, 1)
    ),
    @name := name, @score:= score
FROM (
    SELECT * FROM your_table
    ORDER BY name, score
) tt
CROSS JOIN (SELECT @rank := 1, @name := '', @score := 0) t;

ORDERED FIDDLE

EDIT2:如果您想按位置对它们进行排名，即第一名和第二名。你可以做到这一点。

SELECT 
    id, 
    name, 
    score,
    @rank := if(
        @name = name and @score = score, 
        @rank, 
        if(@name = name, @count + 1, 1)
    ),
    @count := @count + 1,
    if(@name != name, @count := 1, @count),
    @name := name, @score:= score
FROM test
CROSS JOIN (SELECT @rank := 1, @count := 1, @name := '', @score := 0) t;

RANKED FIDDLE