利用窗口函数查询出各小组内排名第一的记录(MySQL)

曾经终败给现在 2022-02-24 11:52 231阅读 0赞

创建测试表：

# 创建销售表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',105),
    (2000,'USA','Computer',1500),
    (2001,'USA','Calculator',50),
    (2001,'USA','Computer',2500),
    (2001,'USA','Computer',1200),
    (2001,'USA','TV',150),
    (2001,'USA','TV',100),
    (2000,'Finland','Computer',1500),
    (2000,'Finland','Phone',100),
    (2001,'Finland','Phone',2500),
    (2000,'India','Calculator',150),
    (2000,'India','Calculator',75),
    (2000,'India','Computer',1200);

问题：从销售表sales中查询出不同年份获利最多的产品的详细信息。

解决办法：借助MySQL中的排名窗口函数，先实现组内排名，再以各组内的排名为条件筛选出符合要求的记录。

先实现组内排名：

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

结果为：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70][]

以组内排名为条件做进一步的筛选：

select year,country,product,profit from 
    (select year,country,product,profit,
    dense_rank() over (partition by year order by profit desc) as group_rank from sales) as temp 
    where group_rank=1;

结果为：

![20190403231911635.png][]

补充：本文以查询出各小组内排名第一的记录为例。如果查询要求更改为查询各小组内前N高的记录，我们只需更改前文代码中外层查询的筛选条件即可。

参考：

[https://blog.csdn.net/qq\_41080850/article/details/86310311][https_blog.csdn.net_qq_41080850_article_details_86310311]

PS：关于MySQL窗口函数的详细介绍，可以参考MySQL窗口函数的官方文档。

MySQL窗口函数官方文档地址：[https://dev.mysql.com/doc/refman/8.0/en/window-functions.html][https_dev.mysql.com_doc_refman_8.0_en_window-functions.html]

MySQL窗口函数官方文档译文地址：[https://blog.csdn.net/qq\_41080850/article/category/8607891][https_blog.csdn.net_qq_41080850_article_category_8607891]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70]: /images/20220224/6b479c4e259845558b987bcc6926faba.png
[20190403231911635.png]: /images/20220224/6e13351b4d374c29ad37e6c40ea48bde.png
[https_blog.csdn.net_qq_41080850_article_details_86310311]: https://blog.csdn.net/qq_41080850/article/details/86310311
[https_dev.mysql.com_doc_refman_8.0_en_window-functions.html]: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
[https_blog.csdn.net_qq_41080850_article_category_8607891]: https://blog.csdn.net/qq_41080850/article/category/8607891