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

相关文章

SQL注入漏洞扫描之sqlmap详解

SQL注入漏洞扫描之sqlmap详解

《SQL注入漏洞扫描之sqlmap详解》SQLMap是一款自动执行SQL注入的审计工具,支持多种SQL注入技术,包括布尔型盲注、时间型盲注、报错型注入、联合查询注入和堆叠查询注入... 目录what支持类型how---less-1为例1.检测网站是否存在sql注入漏洞的注入点2.列举可用数据库3.列举数据库
阅读更多...
Mysql虚拟列的使用场景

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2
阅读更多...
mysql数据库分区的使用

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的
阅读更多...
MySQL中时区参数time_zone解读

MySQL中时区参数time_zone解读

《MySQL中时区参数time_zone解读》MySQL时区参数time_zone用于控制系统函数和字段的DEFAULTCURRENT_TIMESTAMP属性,修改时区可能会影响timestamp类型... 目录前言1.时区参数影响2.如何设置3.字段类型选择总结前言mysql 时区参数 time_zon
阅读更多...
Python MySQL如何通过Binlog获取变更记录恢复数据

Python MySQL如何通过Binlog获取变更记录恢复数据

《PythonMySQL如何通过Binlog获取变更记录恢复数据》本文介绍了如何使用Python和pymysqlreplication库通过MySQL的二进制日志（Binlog）获取数据库的变更记录... 目录python mysql通过Binlog获取变更记录恢复数据1.安装pymysqlreplicat
阅读更多...
使用SQL语言查询多个Excel表格的操作方法

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3
阅读更多...
Mysql DATETIME 毫秒坑的解决

Mysql DATETIME 毫秒坑的解决

《MysqlDATETIME毫秒坑的解决》本文主要介绍了MysqlDATETIME毫秒坑的解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着... 今天写代码突发一个诡异的 bug，代码逻辑大概如下。1. 新增退款单记录boolean save = s
阅读更多...
mysql-8.0.30压缩包版安装和配置MySQL环境过程

mysql-8.0.30压缩包版安装和配置MySQL环境过程

《mysql-8.0.30压缩包版安装和配置MySQL环境过程》该文章介绍了如何在Windows系统中下载、安装和配置MySQL数据库,包括下载地址、解压文件、创建和配置my.ini文件、设置环境变量... 目录压缩包安装配置下载配置环境变量下载和初始化总结压缩包安装配置下载下载地址：https://d
阅读更多...
MySQL中的锁和MVCC机制解读

MySQL中的锁和MVCC机制解读

《MySQL中的锁和MVCC机制解读》MySQL事务、锁和MVCC机制是确保数据库操作原子性、一致性和隔离性的关键,事务必须遵循ACID原则,锁的类型包括表级锁、行级锁和意向锁,MVCC通过非锁定读和... 目录mysql的锁和MVCC机制事务的概念与ACID特性锁的类型及其工作机制锁的粒度与性能影响多版本
阅读更多...
MYSQL行列转置方式

MYSQL行列转置方式

《MYSQL行列转置方式》本文介绍了如何使用MySQL和Navicat进行列转行操作,首先,创建了一个名为`grade`的表,并插入多条数据,然后,通过修改查询SQL语句,使用`CASE`和`IF`函... 目录mysql行列转置开始列转行之前的准备下面开始步入正题总结MYSQL行列转置环境准备：mysq
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2