SQL练习笔记二：分数排名-技术圈

点击上方蓝字关注「网优小兵玩Python」

◆ ◆ ◆

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建score表：

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);

根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

-- 解法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;