SQL练习笔记二：分数排名

CREATE TABLE score ( Id INT ( 4 ) NOT NULL, Score DOUBLE NOT NULL );

INSERT INTO score VALUES(1,3.50);
INSERT INTO score VALUES(2,3.65);
INSERT INTO score VALUES(3,4.00);
INSERT INTO score VALUES(4,3.85);
INSERT INTO score VALUES(5,4.00);
INSERT INTO score VALUES(6,3.65);

-- 解法1 --
SELECT
  Score,
  ( SELECT count( DISTINCT Score ) FROM score WHERE Score >= s.Score ) AS 'Rank'
FROM
  score AS s 
ORDER BY
  Score DESC;

-- 解法2：根据Score分组后排降序，再按序增加1个Rank字段，得出来的结果与原score表进行关联查询后按照Rank值排升序 --
SET @Rank = 0;
SELECT
  score.Score,
  b.Rank 
FROM
  (
  SELECT
    @Rank := @Rank + 1 AS 'Rank',
    a.Score 
  FROM
    ( SELECT Score FROM score GROUP BY Score ORDER BY Score DESC ) AS a 
  ) AS b,
  score 
WHERE
  score.Score = b.Score 
ORDER BY
  b.Rank;

-- 先统计比每个分数高的有几个，然后再加1 --
SELECT
  Score,
  ( SELECT COUNT( Score ) FROM score AS s2 WHERE s2.Score > s1.Score ) + 1 AS 'RANK' 
FROM
  score AS s1 
ORDER BY
  Score DESC;