MySQL利用窗口函数解决排名问题

秒速五厘米 2022-03-27 02:23 384阅读 0赞

从MySQL8.0开始，MySQL增加了对窗口函数的支持。本文主要介绍如何利用窗口函数row\_number(),dense\_rank()和rank()解决相关排名问题。

*说明：本文中的SQL代码均测试于MySQL8.0.13环境。*

### **在总体上进行排名：** ###

# 创建学生成绩表：
    create table grade(
    id int,
    name varchar(10),
    score int (10)
    )engine myisam charset utf8mb4;
     
    # 向成绩表中插入数据：
    insert into grade values
    (1,'张无忌',85),
    (2,'李隆基',59),
    (3,'王五',60),
    (4,'曹操',79),
    (5,'小明',90),
    (6,'如花',60),
    (7,'尉迟恭',100),
    (8,'欧阳风',90),
    (9,'刘备',90),
    (10,'董永',99),
    (11,'冯钰',83),
    (12,'孙殿英',82);

查看grade表中的信息：

select * from grade;

结果：

![2019011221505514.PNG][]

**（1）查询所有学生的成绩信息和排名情况(普通排名，无任何约束条件）：**

select id,name,score,row_number() over (order by score desc) as rank1 from grade;

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70][]

**（2）查询所有学生的成绩信息和排名情况（分数相同的学生，他们的排名也相同，且排名之间没有间隔）：**

select id,name,score,dense_rank() over (order by score desc) as rank2 from grade;

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 1][]

**（3）查询所有学生的成绩信息和排名情况（分数相同的学生，他们的排名也相同，且排名之间有间隔）：**

select id,name,score,rank() over (order by score desc) as rank3 from grade;

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 2][]

**（4）查询所有学生的成绩信息和排名情况（不允许出现相同的排名，若有分数相同的学生，则根据学生姓名的拼音对相同的分数再次排序）：**

select id,name,score,dense_rank() over (order by score desc,convert(name using gbk) asc) as rank4 from grade;

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 3][]

### 在组内进行排名： ###

# 创建销售表sales:
    create table sales(
    year    int,
    country varchar(20),
    product varchar(32),
    profit  int
    )engine myisam charset utf8mb4;
    
    
    # 向销售表sales中插入数据：
    insert into sales values
    (2000,'USA','Calculator',75),
    (2000,'USA','Computer',1500),
    (2001,'USA','Calculator',50),
    (2001,'USA','Computer',1500),
    (2001,'USA','Computer',1200),
    (2001,'USA','TV',150),
    (2001,'USA','TV',100),
    (2000,'Finland','Computer',1500),
    (2000,'Finland','Phone',100),
    (2001,'Finland','Phone',10),
    (2000,'India','Calculator',150),
    (2000,'India','Calculator',75),
    (2000,'India','Computer',1200);

查看sales表中的信息：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 4][]

**查询不同年份内产品的销售信息及利润排名，如果在同一年内有相同排名的利润，则根据产品的来源国家对相同的利润再次进行排序：**

select year,country,product,profit,
    dense_rank() over (partition by year order by profit desc,country asc) as group_rank from sales;

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 5][]

参考：

[https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html][https_dev.mysql.com_doc_refman_8.0_en_window-function-descriptions.html]

[https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html][https_dev.mysql.com_doc_refman_8.0_en_window-functions-usage.html]

[https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html][https_dev.mysql.com_doc_refman_8.0_en_window-functions-named-windows.html]

[2019011221505514.PNG]: /images/20220327/d6658b74a86946fd8903628f1061fcd9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70]: /images/20220327/c4ab9669f0184e3590c7f3318e6abbdd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 1]: /images/20220327/f71757a04f54443795e4b19543e3fd3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 2]: /images/20220327/3f3cd9863def470bb1b2004e1d3fcf4a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 3]: /images/20220327/dd2508c64fa04471af939b0699097e0e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 4]: /images/20220327/fddfb3d637f34a539dadcf833964afb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 5]: /images/20220327/bc7621114674420bbb8701ec2d3e8527.png
[https_dev.mysql.com_doc_refman_8.0_en_window-function-descriptions.html]: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
[https_dev.mysql.com_doc_refman_8.0_en_window-functions-usage.html]: https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
[https_dev.mysql.com_doc_refman_8.0_en_window-functions-named-windows.html]: https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html