SQL应用题（二）之分数排名

题目

编写一个 SQL 查询来实现分数排名。

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

+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+

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

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

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank

解题思路

方法一

首先看到这道题，主要需要我们做的时挨个比较每个分数，然后排序，如果有相同的分数，排序不增加，继续下面的排序，想到了 @，：= 符号的作用，设置变量并赋初始值。具体思路如下：

1.有点类似Java的自增，设置一个变量并赋初值，循环一次自增加1，从而实现排序；

2.MySQL是先将数据查询出来并按照需要排序字段按升序 ASC 或者降序 DESC 排序，设置好排序的初始值为0；

3.将已经排序好的数据从第一条依次取出，取一条就自增加1，实现1到最后的排名；

4.当出现相同的排名时，排名不变，则需要再设置一个变量，用来记录上一条数据的值，跟当前的数据进行比较，如果相同则排名不变，不相同则排名加1。

解题逻辑相对复杂，直接上代码吧。

SELECT s.Score AS Score, CASE WHEN @rowtotal = s.Score THEN @rownum WHEN @rowtotal := s.Score THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS Rank FROM ( SELECT Score FROM scores ORDER BY Score DESC ) AS s, (SELECT @rownum := 0,@rowtotal := NULL) r

方法二

上面方法比较复杂，理解起来也是挺让人头秃的，有一个更好理解的，来，直接上代码：

SELECT s.Score Score, ( SELECT COUNT(DISTINCT sc.Score) FROM scores sc WHERE sc.Score >= s.Score ) AS `Rank` FROM scores s ORDER BY `Rank` ASC

分析SQL

先看里面的一层SQL，

SELECT COUNT(DISTINCT sc.Score) FROM scores sc WHERE sc.Score >= s.Score

先是查询所有分数，按照结果顺序依次跟每一个分数比较，计算大于等于该分数的分数，每次比较的结果分为一组，然后对每组的结果分数去重求总数，就是它的排名

| Score | +-------+ | 4.00 | | 4.00 | | 3.85 | | 3.65 | | 3.65 | | 3.50 | +-------+

最后对排名进行升序排序得出最后结果，这种方法类似Java的双层嵌套循环，遍历数据比较大小求和，理解起来还是很容易的

方法三

上面都是用MySQL解题的，其实使用Sql Server去解决是最简单的，一句SQL就完事儿，上代码：

select Score,dense_rank() over(order by Score DESC ) as Rank from Scores

主要用到的是dense_rank()函数，用于解决这道题再合适不过了，Sql Server还有其他集中排名函数，分别是 ROW_NUMBER()，RANK()，NTILE()

1.ROW_NUMBER() 的用法是先通过 OVER 实现排序，然后按照这个顺序生成排序号，可以用于分页，例如：

select ROW_NUMBER() OVER(order by SubTime desc) as row_num,* from Order

2.RANK() 函数与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，代码如下：

select RANK() OVER(order by UserId) as rank,* from Order

查询结果如下图：

| rank | userId | +-------+------+ | 1 | 1 | | 1 | 1 | | 3 | 2 | | 4 | 3 | | 4 | 3 | | 6 | 4 | +-------+------+

拓展

既然已经提到第一种方法了，这里就拓展一下，SQL的几种排名方法，主要讲MySQL数据语言的。

（1）依次递增排名

要用到 @，:= ，给 rownum 赋值，逻辑前面已经提到过，可以参考方法一的第3点，直接上代码：

SELECT s.Score AS Score, @rownum := @rownum + 1 AS Rank FROM ( SELECT Score FROM scores ORDER BY Score DESC ) AS s, (SELECT @rownum := 0) r

查询结果如下，

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

（2）数据相同，排名相同，排名无间隔

这种排名方法，就是方法一的排名方法，可以直接参考，这里不再赘述

（3）数据相同，排名相同，排名有间隔

先上查询结果，展示下效果，

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

这种方法是在在第二种方法的基础，还要多一个变量记录排序的号码 incrnum，直接看代码，

SELECT obj_new.Score, obj_new.Rank FROM ( SELECT obj.Score, @rownum := @rownum + 1 AS num_tmp, @incrnum := CASE WHEN @rowtotal = obj.score THEN @incrnum WHEN @rowtotal := obj.score THEN @rownum END AS Rank FROM ( SELECT score FROM scores ORDER BY score DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 ) r ) AS obj_new

END

以上就是今天的SQL应用题啦，关于SQL排名的测试题，有不对的地方，望指正