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

2024-03-20 18:36

本文主要是介绍【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 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

postgresql使用UUID函数的方法

《postgresql使用UUID函数的方法》本文给大家介绍postgresql使用UUID函数的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录PostgreSQL有两种生成uuid的方法。可以先通过sql查看是否已安装扩展函数,和可以安装的扩展函数

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种