【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。

相关文章

MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...
Mysql实现范围分区表(新增、删除、重组、查看)

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型（范围、哈希、列表、键值）,主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区（Range Partitioning1、新建分区表：2、分
阅读更多...
MySQL 定时新增分区的实现示例

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后，有时候会需要自动创建分区。比如，一些表数据量非常大，有些数据是热点数据，按照日期分区MululbU
阅读更多...
SQL Server配置管理器无法打开的四种解决方法

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一：桌面图标进入方法二：运行窗口进入检查版本号对照表php方法三：查找文件路径方法四：检查 S
阅读更多...
MySQL 删除数据详解(最新整理)

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》：本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
MySQL中查找重复值的实现

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一：使用GROUP BY和HAVING子句方法二：仅返回重复值方法三：返回完整记录方法四：
阅读更多...
Python内置函数之classmethod函数使用详解

Python内置函数之classmethod函数使用详解

《Python内置函数之classmethod函数使用详解》：本文主要介绍Python内置函数之classmethod函数使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录1. 类方法定义与基本语法2. 类方法 vs 实例方法 vs 静态方法3. 核心特性与用法(1编程客
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
Python函数作用域示例详解

Python函数作用域示例详解

《Python函数作用域示例详解》本文介绍了Python中的LEGB作用域规则,详细解析了变量查找的四个层级,通过具体代码示例,展示了各层级的变量访问规则和特性,对python函数作用域相关知识感兴趣... 目录一、LEGB 规则二、作用域实例2.1 局部作用域（Local）2.2 闭包作用域（Enclos
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2