Mysql四大排名函数

心已赠人 2022-09-09 09:17 189阅读 0赞

题目来自 LeetCode ：

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

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

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

原表                         查询结果
    +----+-------+               +-------+------+
    | Id | Score |               | Score | Rank |
    +----+-------+               +-------+------+
    | 1  | 3.50  |		  |\     | 4.00  | 1    |
    | 2  | 3.65  |    ----| \    | 4.00  | 1    |
    | 3  | 4.00  |    ----| /    | 3.85  | 2    |
    | 4  | 3.85  |        |/     | 3.65  | 3    |
    | 5  | 4.00  |               | 3.65  | 3    |
    | 6  | 3.65  |               | 3.50  | 4    |
    +----+-------+               +-------+------+

官方给出的题解为：

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

除此之外，还可以使用数据库内置的排名函数。

mysql内置4个排名函数，根据题目要求此题因选择 dense\_rank 。

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

--------------------

下面实例演示这四种排名函数的区别。

1.  首先建表，要求有字段 id，score即可。  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16]
2.  插入数据。将例题中给出的数据插入即可。  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]
3.  使用四种排名函数对以上数据排名，观察区别。  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]

可以看到，在我们查询到的结果中：

*  rank：会输出所有元素正确的排名，但是相同的值会并列。因此，会出现断层。
 *  dense\_rank：在rank的基础上，使排名紧凑。解决断层现象。
 *  row\_number：会输出元素的正确排名，相同的值不会并列。
 *  ntile：将有序分区的行分配到指定数量的大致相等的组内，形成梯队。  
    其中，ntile(3) 是指将结果分为三组（三个梯队）。

例如：有如下表结构和数据。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]  
使用 ntile 进行排名。

1）分一个组：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
2）分两个组：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 5]  
3）分个三组：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 6]  
4）分10个组：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 7]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/39560c4066994e69a34b7224e0174c02.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/8b4a5ee111eb446b83a660ad81b48ab2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220829/45dd7f68df5b4d94b077c9162c7094df.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220829/7288ba0e8fe74b5a85b9b8ededd96d3c.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220829/1f6cf1216b1d43ea93100982ca178781.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220829/382befc8c644402d82d946c540871bd0.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 6]: /images/20220829/a898933c5ba64623a92056cb7b2e7cc7.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oiR5Y-rUlQ_size_20_color_FFFFFF_t_70_g_se_x_16 7]: /images/20220829/e88c660ad4e74c17b5b098a40f925324.png