【MySQL】巧用 Max 函数【2】最好的三家酒庄

2024-03-20 18:36
文章标签 mysql 函数 最好 巧用 database max 三家 酒庄

本文主要是介绍【MySQL】巧用 Max 函数【2】最好的三家酒庄，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

力扣题

1、题目地址

2991. 最好的三家酒庄

2、模拟表

表：Wineries

Column NameType
idint
countryvarchar
pointsint
wineryvarchar
  • id 是这张表具有唯一值的列。
  • 这张表包含 id, country, points,和 winery。

3、要求

编写一个解决方案，根据每家酒庄的 总分 找出 每个国家 的 前三名酒庄。

如果有 多个酒庄 的总分 相同，则按 winery 名称升序排列。

如果没有 分数排在第二的酒庄，则输出 ‘No Second Winery’，

如果没有 分数排在第三的酒庄，则输出 ‘No Third Winery’。

返回结果表按 country 升序 排列。

4、示例

输入：

Wineries 表：

idcountrypointswinery
103Australia84WhisperingPines
737Australia85GrapesGalore
848Australia100HarmonyHill
222Hungary60MoonlitCellars
116USA47RoyalVines
124USA45Eagle’sNest
648India69SunsetVines
894USA39RoyalVines
677USA9PacificCrest

输出：

countrytop_winerysecond_winerythird_winery
AustraliaHarmonyHill (100)GrapesGalore (85)WhisperingPines (84)
HungaryMoonlitCellars (60)No second wineryNo third winery
IndiaSunsetVines (69)No second wineryNo third winery
USARoyalVines (86)Eagle’sNest (45)PacificCrest (9)

解释：

对于 Australia

  • HarmonyHill 酒庄获得了 Australia 的最高分数，为 100 分。
  • GrapesGalore 酒庄总共获得 85 分，位列 Australia 的第二位。
  • WhisperingPines 酒庄总共获得 80 分，位列 Australia 的第三位。

对于 Hungary

  • MoonlitCellars 是唯一的酒庄，获得 60 分，自动成为最高分数的酒庄。没有第二或第三家酒庄。

对于 India

  • SunsetVines 是唯一的酒庄，获得 69 分，成为最高的酒庄。没有第二或第三家酒庄。

对于 USA

  • RoyalVines Wines 累计了总分 47 + 39 = 86 分，占据了 USA 的最高位置。
  • Eagle’sNest 总共获得 45 分，位列 USA 的第二高位置。
  • PacificCrest 累计了 9 分，位列 USA 的第三高酒庄。

输出表按国家首字母升序排列。

5、代码编写

要求分析

第一步：因为存在 country 和 winery 相同的情况，需要先分组求和先

select country, winery, sum(points) AS points
from Wineries
group by country, winery

第二步：根据要求进行分组排序，可以使用窗口函数 row_number 标序号也是排序的效果，根据 country 取分组，然后因为要取前三名，所以使用 points 倒序，然后如果多个酒庄总分相同，则按 winery 升序

with tmp as (select *, row_number() over(partition by country order by points desc, winery) as rnfrom (select country, winery, sum(points) AS pointsfrom Wineriesgroup by country, winery) AS one
)

第三步：首先我想着是按 country 直接分组，按第二步查询出来的 rn 序号去分别取值，但是有个问题，如下两种写法

select country, if(rn=1, concat(winery, ' (', points, ')'), 'No first winery') AS top_winery,if(rn=2, concat(winery, ' (', points, ')'), 'No second winery') AS second_winery,if(rn=3, concat(winery, ' (', points, ')'), 'No third winery') AS third_winery
from tmp
group by 1

| country   | top_winery          | second_winery    | third_winery    |
| --------- | ------------------- | ---------------- | --------------- |
| Australia | HarmonyHill (100)   | No second winery | No third winery |
| Hungary   | MoonlitCellars (60) | No second winery | No third winery |
| India     | SunsetVines (69)    | No second winery | No third winery |
| USA       | RoyalVines (86)     | No second winery | No third winery |

select country, if(rn=1, concat(winery, ' (', points, ')'), null) AS top_winery,if(rn=2, concat(winery, ' (', points, ')'), null) AS second_winery,if(rn=3, concat(winery, ' (', points, ')'), null) AS third_winery
from tmp
group by 1

| country   | top_winery          | second_winery | third_winery |
| --------- | ------------------- | ------------- | ------------ |
| Australia | HarmonyHill (100)   | null          | null         |
| Hungary   | MoonlitCellars (60) | null          | null         |
| India     | SunsetVines (69)    | null          | null         |
| USA       | RoyalVines (86)     | null          | null         |

会发现一个点，后面的值都不见了，这是因为 group by 的缘故，取到 rn=1，之后就取不到 rn=2 和 rn=3 了，只能让一行生效

然后我就想着要不把 group by 去掉试试

select country, if(rn=1, concat(winery, ' (', points, ')'), 'No first winery') AS top_winery,if(rn=2, concat(winery, ' (', points, ')'), 'No second winery') AS second_winery,if(rn=3, concat(winery, ' (', points, ')'), 'No third winery') AS third_winery
from tmp

会发现一个点，就是如果只保留不同 country 组里面，竖行只保留有值的就行，竖行如果都是 null，就单剩 null 就行

| country   | top_winery          | second_winery     | third_winery         |
| --------- | ------------------- | ----------------- | -------------------- |
| Australia | HarmonyHill (100)   | No second winery  | No third winery      |
| Australia | No first winery     | GrapesGalore (85) | No third winery      |
| Australia | No first winery     | No second winery  | WhisperingPines (84) |
| Hungary   | MoonlitCellars (60) | No second winery  | No third winery      |
| India     | SunsetVines (69)    | No second winery  | No third winery      |
| USA       | RoyalVines (86)     | No second winery  | No third winery      |
| USA       | No first winery     | Eagle'sNest (45)  | No third winery      |
| USA       | No first winery     | No second winery  | PacificCrest (9)     |

这就可以引入 Max 函数的巧用了，作用是可以将 null 值排去，我们需要将 if 后面的值改成 null，且将 if 包在 max 里，然后再分组

第四步：if 的第三个参数改成 null

select country, if(rn=1, concat(winery, ' (', points, ')'), null) AS top_winery,if(rn=2, concat(winery, ' (', points, ')'), null) AS second_winery,if(rn=3, concat(winery, ' (', points, ')'), null) AS third_winery
from tmp

| country   | top_winery          | second_winery     | third_winery         |
| --------- | ------------------- | ----------------- | -------------------- |
| Australia | HarmonyHill (100)   | null              | null                 |
| Australia | null                | GrapesGalore (85) | null                 |
| Australia | null                | null              | WhisperingPines (84) |
| Hungary   | MoonlitCellars (60) | null              | null                 |
| India     | SunsetVines (69)    | null              | null                 |
| USA       | RoyalVines (86)     | null              | null                 |
| USA       | null                | Eagle'sNest (45)  | null                 |
| USA       | null                | null              | PacificCrest (9)     |

第五步：再加上 max 函数 和 group by 分组（相当于是不同 country 分组，竖行如果有值优先保留有值，如果全为 null，则返回 null）

with tmp2 as (select country, max(if(rn=1, concat(winery, ' (', points, ')'), null)) AS top_winery,max(if(rn=2, concat(winery, ' (', points, ')'), null)) AS second_winery,max(if(rn=3, concat(winery, ' (', points, ')'), null)) AS third_wineryfrom tmpgroup by 1
)

| country   | top_winery          | second_winery     | third_winery         |
| --------- | ------------------- | ----------------- | -------------------- |
| Australia | HarmonyHill (100)   | GrapesGalore (85) | WhisperingPines (84) |
| Hungary   | MoonlitCellars (60) | null              | null                 |
| India     | SunsetVines (69)    | null              | null                 |
| USA       | RoyalVines (86)     | Eagle'sNest (45)  | PacificCrest (9)     |

第六步：将 null 值转换为需要的值，再按 country 升序即可

select country, top_winery, ifnull(second_winery, 'No second winery') AS second_winery,ifnull(third_winery, 'No third winery') AS third_winery
from tmp2
order by 1

我的代码

with tmp as (select *, row_number() over(partition by country order by points desc, winery) as rnfrom (select country, winery, sum(points) AS pointsfrom Wineriesgroup by country, winery) AS one
), tmp2 as (select country, max(if(rn=1, concat(winery, ' (', points, ')'), null)) AS top_winery,max(if(rn=2, concat(winery, ' (', points, ')'), null)) AS second_winery,max(if(rn=3, concat(winery, ' (', points, ')'), null)) AS third_wineryfrom tmpgroup by 1
)
select country, top_winery, ifnull(second_winery, 'No second winery') AS second_winery,ifnull(third_winery, 'No third winery') AS third_winery
from tmp2
order by 1

这篇关于【MySQL】巧用 Max 函数【2】最好的三家酒庄的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/830377。

相关文章

Ubuntu中远程连接Mysql数据库的详细图文教程

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.
阅读更多...
基于SpringBoot+Mybatis实现Mysql分表

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进
阅读更多...
Python3.6连接MySQL的详细步骤

Python3.6连接MySQL的详细步骤

《Python3.6连接MySQL的详细步骤》在现代Web开发和数据处理中,Python与数据库的交互是必不可少的一部分,MySQL作为最流行的开源关系型数据库管理系统之一,与Python的结合可以实... 目录环境准备安装python 3.6安装mysql安装pymysql库连接到MySQL建立连接执行S
阅读更多...
Kotlin 作用域函数apply、let、run、with、also使用指南

Kotlin 作用域函数apply、let、run、with、also使用指南

《Kotlin作用域函数apply、let、run、with、also使用指南》在Kotlin开发中,作用域函数（ScopeFunctions）是一组能让代码更简洁、更函数式的高阶函数,本文将... 目录一、引言：为什么需要作用域函数？二、作用域函China编程数详解1. apply：对象配置的 “流式构建器”最
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
MySQL错误代码2058和2059的解决办法

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》：本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法（敲重点！！！）1. php前置理解2058和2059的错误
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2