MySQL分数排名

梦里梦外; 2023-10-06 18:46 36阅读 0赞

#### MySQL分数排名 ####

*  SQL架构
 *  题目描述
 *  题解
 *   *  答1：Mysql比较好理解的一种写法
     *  答2
     *  答3：临时变量 + 联查 实现
     *   *  第一步：先查分数对应的排名
         *  第二步：和原有的Scores表联查，再根据rank字段排序即可
 *  知识点
 *   *  MySQL的@与@@区别
     *  mysql中:=和=的区别

## SQL架构 ##

Create table If Not Exists 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');

## 题目描述 ##

编写一个 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：Mysql比较好理解的一种写法 ###

对于a表中的每一个分数score，找出b表中有多少个大于或等于该分数的不同的分数，然后按降序排列

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 Score DESC;
    
    +-------+------+
    | score | rank |
    +-------+------+
    |  4.00 |    1 |
    |  4.00 |    1 |
    |  3.85 |    2 |
    |  3.65 |    3 |
    |  3.65 |    3 |
    |  3.50 |    4 |
    +-------+------+
    6 rows in set (0.00 sec)

### 答2 ###

select s1.Score, 
    (
    	select count(distinct s2.Score)  from Scores s2  where s2.Score > s1.Score )
    	+1 `Rank` from Scores s1 order by Score DESC;
    	
    +-------+------+
    | Score | Rank |
    +-------+------+
    |  4.00 |    1 |
    |  4.00 |    1 |
    |  3.85 |    2 |
    |  3.65 |    3 |
    |  3.65 |    3 |
    |  3.50 |    4 |
    +-------+------+
    6 rows in set (0.00 sec)

### 答3：临时变量 + 联查 实现 ###

我看题解了里面大部分都是用了 DENSE\_RANK() 函数实现的，不过 mysql 5.x 版本好像不支持，所以用临时变量加联查的方式来实现感觉也不错，虽然通不过本题，但是应该是正确的做法，我的mysql版本是 5.6.45

#### 第一步：先查分数对应的排名 ####

select 
    @Rank := @Rank + 1 AS Rank,
    s.Score
    from 
    ( select @Rank := 0 ) m,
    ( select Score from Scores group by Score desc) s
    
    +------+-------+
    | Rank | Score |
    +------+-------+
    |    1 |  4.00 |
    |    2 |  3.85 |
    |    3 |  3.65 |
    |    4 |  3.50 |
    +------+-------+
    4 rows in set, 1 warning (0.00 sec)

#### 第二步：和原有的Scores表联查，再根据rank字段排序即可 ####

select s.Score, r.Rank 
    from Scores s
    left join
    (
        select 
        @Rank := @Rank + 1 AS Rank,
        s.Score
        from 
        ( select @Rank := 0 ) m,
        ( select Score from Scores group by Score desc) s
    ) r
    on s.Score = r.Score
    order by r.Rank;
    
    +-------+------+
    | Score | Rank |
    +-------+------+
    |  4.00 |    1 |
    |  4.00 |    1 |
    |  3.85 |    2 |
    |  3.65 |    3 |
    |  3.65 |    3 |
    |  3.50 |    4 |
    +-------+------+
    6 rows in set, 1 warning (0.00 sec)

( select @Rank := 0 ) m, 这个表，是在给临时变量赋初始值。

## 知识点 ##

### MySQL的@与@@区别 ###

@x 是 用户自定义的变量  (User variables are written as @var_name)
    
    @@x 是 global或session变量  (@@global  @@session )
    
    @@查看全局变量:
    select   @@log_error;
    
    @设置全局变量值:
    
    mysql> SET @t1=0, @t2=0, @t3=0;
    
    mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

### mysql中:=和=的区别 ###

`只有在 set 和 update 时才是和 := 一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=`

:=  
`不只在set和update时时赋值的作用，在select也是赋值的作用。`

**@num:=@num+1,:=是赋值的作用，所以，先执行@num+1,然后再赋值给@num，所以能正确实现行号的作用。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]  
**@num=@num+1,此时=是等于的作用，@num不等于@num+1，所以始终返回0，如果改为@num=@num,始终返回1了。mysql数据库中，用1表示真，0表示假。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2e75f8e4b30443f4ac23e4713800dc7f.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/6318a6c05e9046f58ed22a704080891a.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70