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

相关文章

Linux下MySQL数据库定时备份脚本与Crontab配置教学

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定
阅读更多...
C++统计函数执行时间的最佳实践

C++统计函数执行时间的最佳实践

《C++统计函数执行时间的最佳实践》在软件开发过程中,性能分析是优化程序的重要环节,了解函数的执行时间分布对于识别性能瓶颈至关重要,本文将分享一个C++函数执行时间统计工具,希望对大家有所帮助... 目录前言工具特性核心设计1. 数据结构设计2. 单例模式管理器3. RAII自动计时使用方法基本用法高级用法
阅读更多...
MySQL中On duplicate key update的实现示例

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP
阅读更多...
MySQL分库分表的实践示例

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分
阅读更多...
Python与MySQL实现数据库实时同步的详细步骤

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述：数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1
阅读更多...
GO语言中函数命名返回值的使用

GO语言中函数命名返回值的使用

《GO语言中函数命名返回值的使用》在Go语言中,函数可以为其返回值指定名称,这被称为命名返回值或命名返回参数,这种特性可以使代码更清晰,特别是在返回多个值时,感兴趣的可以了解一下... 目录基本语法函数命名返回特点代码示例命名特点基本语法func functionName(parameters) (nam
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
Python Counter 函数使用案例

Python Counter 函数使用案例

《PythonCounter函数使用案例》Counter是collections模块中的一个类,专门用于对可迭代对象中的元素进行计数,接下来通过本文给大家介绍PythonCounter函数使用案例... 目录一、Counter函数概述二、基本使用案例（一）列表元素计数（二）字符串字符计数（三）元组计数三、C
阅读更多...
MySQL 表空却 ibd 文件过大的问题及解决方法

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景：表空却 “吃满” 磁盘的怪事二、问题复现：一步步编程还原异常场景1. 准备测试源表与数据
阅读更多...
Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2