LeetCode DATABASE - 178

Scroll Down

题目

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

建表语句

Create table   Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');

题目解释

其实就是一个成绩表,需要按照成绩从高到低进行排序,并且排名不断

这里如果用自带函数其实很容易,使用Dense_rank() over(),dense_rank() over()就是为了此种场景而生的,over()中可以用分组也可以不分组进行排序,下面是此题答案.

SELECT Score, dense_rank() OVER( ORDER BY score DESC ) as Ranks FROM  scores ;

最终结果是

database178

如果不使用dense_rank()over()也是可以实现的

select id,Score,(select count( distinct t2.Score)  from Scores  t2 where t2.Score >= t1.Score  ) from Scores  t1 order by Score desc

database178-2

rank() over() 和 row_number() over()介绍

rank()over()其实就是排序并断号,比如排名为1,1,3,4,5,5,7这样的效果,row_number()over()则是无论是否相同成绩,都是按照顺序来,1,2,3,4,5,6

下面看一下这两种在测试数据上的效果怎么样

rank() over()

SELECT ID,score, RANK() OVER( ORDER BY score DESC ) as RANKS FROM  scores ;

database178-3

当然如果不用rank()over()也是可以实现的,类似的不用函数的话

select id,Score,(select count(*) + 1 from Scores  t2 where t2.Score > t1.Score  ) from Scores  t1 order by Score desc

row_number() over()

SELECT ID,score,row_number() OVER(ORDER BY score DESC)  as Ranks FROM scores;

database178-4

类似的不用函数的话

SELECT ID,Score,(@i := @i + 1) FROM scores t1,(select @i := 0) as rowTable ORDER BY score DESC

这里是使用了一个变量的方式,每过一行就给变量加1,然后根据分数倒序就可以了

** 当然dense_rank() over() ,rank() over() ,row_number() over() 都是可以进行分组判断的,rank() over(partition by id order by score desc) ** 只是本张表里面没有相同的id,分组和不分组的效果是一样的,如果这里面加上课程的维度的话,就可以对每个人的每门课程进行排序