CREATE TABLE mytable (
  `Player` VARCHAR(1),
  `Game` VARCHAR(1),
  `Score` INTEGER
);

INSERT INTO mytable
  (`Player`, `Game`, `Score`)
VALUES
  ('A', 'X', '20'),
  ('A', 'Y', '15'),
  ('A', 'Z', '50'),
  ('B', 'X', '10'),
  ('B', 'Y', '60'),
  ('B', 'Z', '40');

SELECT
`Score`
, `Game`
,IF(@name <> `Player`,@rn := 1, @rn := @rn+1) rank_number
,@name := `Player` 'Player'
FROM
(SELECT * FROM mytable ORDER BY Player,Score DESC) t1,(SELECT @name := '') t2,
(SELECT @rn := 0) t3;

Score | Game | rank_number | Player
----: | :--- | ----------: | :-----
   50 | Z    |           1 | A     
   20 | X    |           2 | A     
   15 | Y    |           3 | A     
   60 | Y    |           1 | B     
   40 | Z    |           2 | B     
   10 | X    |           3 | B     

db<；>此处小提琴