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排名的测试题，有不对的地方，望指正