Leetcode178.分数排名（中等）

题目
编写一个 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

审题
这个题目其实就是对得分Score进行排序，且同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3。
得到的结果为Score Rank两列，且按Score进行降序。

自己的解答
其实在做之前的时候已经得到过分数排名的，只不过是对分数进行了去重。在此基础上和Scores表进行内连接然后按Score排序就可以得到最终的结果，但是这样的想法比较简单，但是感觉效率不高。

自己生成表与数据

CREATE TABLE Scores(
Id INT,
Score FLOAT);

INSERT INTO Scores(Id, Score) VALUE(1, 3.5),(2,3.65),(3,4.0),(4,3.85),(5,4.0),(6,3.65);

SELECT * FROM Scores;

回顾一下之前是怎么得到排名的
1.两表自连接，连接条件设定为表1的score小于等于表2的score
2.以表1的score分组，统计表1中每个salary分组后对应表2中score唯一值个数

SELECT S1.`Score`, COUNT(DISTINCT S2.`Score`) as `Rank`
FROM Scores AS S1
JOIN Scores AS S2
ON S1.`Score` <= S2.`Score`
GROUP BY S1.Score;

下面构造内连接即可

SELECT tmp.*
FROM Scores
JOIN (SELECT S1.`Score`, COUNT(DISTINCT S2.`Score`) as `Rank`
    FROM Scores AS S1
    JOIN Scores AS S2
    ON S1.`Score` <= S2.`Score`
    GROUP BY S1.Score) as `tmp`
ON Scores.`Score` = tmp.Score
ORDER BY tmp.Score DESC;

提交成功

好像还不算太慢

注意点
由于Rank为关键字,所以必须用` `

而且定义变量的方法也是可行的(177提到过)

SELECT @r:=IF(@p=score, @r, @r+1) AS `rank`,  @p:= score AS Score
FROM Scores, (SELECT @r:=0, @p:=NULL)init 
ORDER BY score DESC;

子查询调换一下列即可

# Write your MySQL query statement below
SELECT tmp.Score, tmp.`rank`
FROM (SELECT @r:=IF(@p=score, @r, @r+1) AS `rank`,  @p:= score AS Score
FROM Scores, (SELECT @r:=0, @p:=NULL)init 
ORDER BY score DESC) AS tmp;

额。。。我觉着没啥问题吧
要不就两列合并算了

SELECT S.`Score`, tmp.`rank`
FROM Scores AS S, 
(SELECT @r:=IF(@p=score, @r, @r+1) AS `rank`,  @p:= score AS Score
FROM Scores, (SELECT @r:=0, @p:=NULL)init 
ORDER BY score DESC) AS tmp;

这样得到的笛卡尔乘积，跪了。。

别的思路与解答
1.子查询
分成两个部分写会容易很多

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

2.利用窗口函数
图解SQL面试题：经典排名问题

对以上数据应用窗口函数
1.涉及到排名问题，可以使用窗口函数
2.专用窗口函数rank, dense_rank, row_number有什么区别呢？

它们的区别我举个例子，你们一下就能看懂：

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级

结果如下

ranking 得到的排名是有间隔的
dese_rank 得到的排名是没有间隔的，也就是我们想要的结果
row_num 不管重复数据就是给定行号

select score, 
       dense_rank() over(order by Score desc) as Rank
from Scores;

3.两表自连接
和我的方法类似但不用再次联结了也是1的外连接版本 1是3的子查询版本
按Id和Score分组。每组中，大于等于每个Score的不同Score数目就是其排名。

select S1.Score,count(distinct S2.Score) as `Rank`
from Scores as S1 join Scores as S2 on (S1.Score <= S2.Score)
group by S1.Id,S1.Score
order by S1.Score desc;

为什么要加 group by S1.Id？
因为要对表中每个数据进行排名不然结果就是最小的一个

4.对1可以进行优化
把两表联结中的S2表改变一下，不要用所有的数据，而是去重之后再用

SELECT 
  S1.Score,
  COUNT(DISTINCT S2.Score) AS `Rank` 
FROM
  Scores AS S1 
  JOIN 
    (SELECT DISTINCT 
      Score 
    FROM
      Scores 
    ORDER BY Score DESC) AS S2 
    ON (S1.Score <= S2.Score) 
GROUP BY S1.Id, S1.Score 
ORDER BY S1.Score DESC ;

效率确实高一些吧。

完