题目描述

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

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

表1：Score (Primary Key: Id)

Id Salary
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

相关知识

  1. sql中的变量

    • 声明变量：

      • set方式

        SET @variable_name = value;
SET @
:= value;

      • select方式

        SELECT @variable_name := value;

    • 变量赋值：set @value_name = something，可以用set赋值，也可以用select赋值

    • 变量使用：@variable_name

    • 在常规SQL语句中，使用变量离不开@，在存储例程(routine)中无需@

    • 实现序号的功能

      SELECT score,(@rowno := @rowno + 1) AS rowno
FROM SCORE,(SELECT @rowno :=0) r;

解题过程

  1. 初步思考

    • 先对分数进行降序排列作为表A
    • 对表A进行去重后作为表B
    • 从表A获取分数，从表B获取行号

  2. 编写sql，结果略有差错

    • sql

      WITH CTE AS (SELECT Score FROM Scores ORDER BY Score DESC) 
SELECT s.Score, `Rank` 
FROM
    CTE s,
    (SELECT Score, (@Rank := @Rank+1) AS `Rank` FROM (SELECT DISTINCT Score FROM CTE) rs,(SELECT @Rank :=0) rr) r
WHERE
    s.Score=r.Score

    • my output

      {
    "headers":[
        "Score",
        "Rank"
    ],
    "values":[
        [
            4.00,
            1.0
        ],
        [
            4.00,
            1.0
        ],
        [
            3.85,
            2.0
        ],
        [
            3.65,
            3.0
        ],
        [
            3.65,
            3.0
        ],
        [
            3.50,
            4.0
        ]
    ]
}

    • standard output

      {
    "headers":[
        "Score",
        "Rank"
    ],
    "values":[
        [
            4.00,
            1
        ],
        [
            4.00,
            1
        ],
        [
            3.85,
            2
        ],
        [
            3.65,
            3.0
        ],
        [
            3.65,
            3.0
        ],
        [
            3.50,
            4.0
        ]
    ]
}

    • 可以看出是因为我的行号有小数位，应该是变量类型的问题

  3. 使用CAST()函数将float转换为int类型，即SIGNED类型

    WITH CTE AS (SELECT Score FROM Scores ORDER BY Score DESC) 
SELECT s.Score, CAST(`Rank` AS SIGNED) AS `Rank`
FROM
    CTE s,
    (SELECT Score, (@Rank := (@Rank+1)) AS `Rank` FROM (SELECT DISTINCT Score FROM CTE) rs,(SELECT @Rank :=0 ) rr) r
WHERE
    s.Score=r.Score

  4. 提交，答案正确

学习总结

  1. CAST(value AS type)

    type 备注
    DATE 日期，格式YYYY-MM-DD
    DATETIME 日期加具体时间，格式YYYY-MM-DD HH:MM:SS
    TIME 时间，格式HH:MM:SS
    CHAR 字符型
    SIGNED 有符号int型
    UNSIGNED 无符号int型
    BINARY 二进制型
    DECIMAL float类型

  2. 简单排名(不考虑并列)

    • sql

      SELECT 
	s.Score AS Score,(@rank := @rank +1) AS `Rank`
FROM
	(SELECT * FROM Score ORDER BY Score DESC) s,(SELECT @rank :=0 ) r;

    • 上面这条语句中的Rank如果不加撇号会导致报语法错误，在Mysql中，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号

  3. 并列排名

    • 并列但不占位(1,2,2,3…)

      • 在简单排名的基础上，多创建一个变量，用来记录上一个人的分数，然后通过比较来判断名次是否需要增加

      • sql

        SELECT 
	Score,
	CASE
		WHEN @last_score = Score 
		THEN @rank
		WHEN @last_score := Score
		THEN @rank := @rank + 1
	END AS `Rank`
FROM
	(SELECT Score From Scores ORDER BY Score DESC) u,(SELECT @rank :=0,@last_score:=NULL) r
	-- last_score的初值可以不为NULL，只需是可能分数之外的值

      • 上面这个sql，当第一个元组的Score不是0.00时可以正常执行，但是当第一个元组的Score是0.00时，会返回NULL，这是因为两个WHEN的条件都不成立

        • my output

          {
    "headers":[
        "Score",
        "Rank"
    ],
    "values":[
        [
            0.00,
            null
        ]
    ]
}

        • standard output

          {
    "headers":[
        "Score",
        "Rank"
    ],
    "values":[
        [
            0.00,
            1
        ]
    ]
}

        • 第一个元组执行CASE时，@last_score为NULL，还未被修改过

        • 第一个WHEN：由于无论初始值是什么，它都一定是不会出现在成绩这一列中的值，所以进行逻辑判断后为假，所以这个分支进不去

        • 第二个WHEN：将当前元组的Score赋值给@last_score，并用@last_score此时为0.00，进行逻辑判断，相当于假，所以这个分支也进不去

        • 所以CASE语句的所有分支均未进入，因此返回值为NULL

      • 修改sql，来避免第一个是0.00，且还需避免重复的0.00

        • 当与上一个人分数一样，进入第一个分支，名次不用增加，直接返回；

        • 当与上一个人分数不一样时，将当前元组的分数赋值给@last_score，如果不是0分，逻辑判断为真，名次加一后返回

        • 当与上一个人分数不一样且当前元组的成绩为0分时，进入第三个分支，名次加一后返回

        • sql

          SELECT 
	Score,
	CAST(
        (CASE
		WHEN @last_score = Score 
		THEN @rank
		WHEN @last_score := Score
		THEN @rank := @rank + 1
        WHEN CAST(@last_score AS SIGNED) =0
        THEN @rank :=@rank+1
	    END
        )
        AS SIGNED
    ) AS `Rank`
FROM
	(SELECT Score From Scores ORDER BY Score DESC) u,(SELECT @rank :=0,@last_score:=NULL) r

        • 或者使用OR运算符拼接第二个和第三个分支，注意OR的两侧分别要加括号，否则表达式会是@last_score := Score OR Score = 0.00将会被解析为将Score OR Score = 0.00的结果赋值给@last_score，之后用@last_score作为判断条件

           SELECT 
	Score,
	CAST(
        (CASE
		WHEN @last_score = Score 
		THEN @rank
		WHEN (@last_score := Score) OR (Score = 0.00)
		THEN @rank := @rank + 1
	    END)
        AS SIGNED
    ) AS `Rank`
FROM
	(SELECT Score From Scores ORDER BY Score DESC) u,(SELECT @rank :=0,@last_score:=NULL) r

      • 题解中还有非常精炼的解法

        • sql

          SELECT 
	Score,DENSE_RANK() OVER(ORDER BY Score DESC) AS `Rank` 
FROM 
	Scores

    • 并列且占位(1,2,2,4)

      • 根据上面的学习，编写sql

        SELECT 
	Score,
	CAST(`Rank` AS SIGNED) AS `Rank`
FROM
	(
    	SELECT
        	Score,
        	@rank := @rank+1,
        	@last_rank:= 
        				CASE
        				WHEN @last_score =Score
        				THEN @last_rank
        				WHEN (@last_score:= Score) or (Score =0.00)
        				THEN @rank
        				END
        				AS `Rank`
        FROM
        	(SELECT Score FROM Scores ORDER BY Score DESC) s,
        	(SELECT @rank:=0,@last_score:=NULL,@last_rank:=0) r
               				 
    ) temp

    • SQL自带函数实现上述功能

      • ROW_NUMBER()在各个分区内连续排名，不论并列

      • DENSE_RANK()在各个分区排名，并列者同名次，且连续(1,2,2,3)

      • RANK()在各个分区排名，并列者同名次，但不连续(1,2,2,4)

      • 语法

        SELECT
	ROW_NUMBER()/DENSE_RANK()/RANK()
	OVER(
    	PARTITION BY ...
        ORDER BY ... DESC/ASC ....
    )

      • 如本题可以直接使用系统提供的函数，写法如下

        SELECT
	Score,
	DENSE_RANK()
	OVER(
    	ORDER BY Score DESC
    )
    AS `Rank`
FROM
	Scores