MySQL分组排名

Question

我有以下查询:

SELECT @rn:=@rn+1 AS rank, pet_name, pet_count
FROM (
  SELECT pet_name, COUNT(*) AS pet_count
  FROM pets
  WHERE pet_breed="cat"
  GROUP BY pet_name
  ORDER BY pet_count DESC
) t1, (SELECT @rn:=0) t2

产生这样的输出:

1   Kitty   87
2   Smokey  81
3   Bella   80
4   Oreo    63
5   Charlie 63
6   Tiger   62
7   Lucy    62
8   Jack    61
9   Tigger  60

问题是 Oreo 和 Charlie、Tiger 和 Lucy 应该排名相同，因为他们都是 63，所以结果看起来像

1   Kitty   87
2   Smokey  81
3   Bella   80
4   Oreo    63
4   Charlie 63
5   Tiger   62
5   Lucy    62
6   Jack    61
7   Tigger  60

我只是想不出如何按照我想要的方式进行调整。

建议？

Answer 1

SELECT IF((@previous = pet_count, @rn, @rn:=@rn+1) AS rank, pet_name, pet_count, @previous := pet_count
FROM (
  SELECT pet_name, COUNT(*)  AS pet_count
  FROM pets
  WHERE pet_breed="cat"
  GROUP BY pet_name
  ORDER BY pet_count DESC
) t1, (SELECT @rn:=0) t2

我没有实际尝试过。但是概念会起作用