大数据第31天-MySQL练习题3-杨大伟

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

展示效果：

1 Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
2 
3 insert into Scores (Id, Score) values (1, 3.5);
4 insert into Scores (Id, Score) values (2, 3.65);
5 insert into Scores (Id, Score) values (3, 4.0);
6 insert into Scores (Id, Score) values (4, 3.85);
7 insert into Scores (Id, Score) values (5, 4.0);
8 insert into Scores (Id, Score) values (6, 3.65);

最终SQL:

 1 select 
 2       a.Score as score , 
 3       (select 
 4               count(distinct b.Score) 
 5        from 
 6               Scores b 
 7        where 
 8               b.Score >=a.Score) as rank
 9 from 
10      Scores a 
11 order by 
12      Score DESC;

大数据第31天-MySQL练习题3-杨大伟

相关推荐