大数据第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;
