本文主要是介绍【MySQL】巧用 Max 函数【2】最好的三家酒庄，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
力扣题
1、题目地址
2991. 最好的三家酒庄
2、模拟表
表：Wineries
| Column Name | Type |
|---|---|
| id | int |
| country | varchar |
| points | int |
| winery | varchar |
- id 是这张表具有唯一值的列。
- 这张表包含 id, country, points,和 winery。
3、要求
编写一个解决方案，根据每家酒庄的 总分 找出 每个国家 的 前三名酒庄。
如果有 多个酒庄 的总分 相同，则按 winery 名称升序排列。
如果没有 分数排在第二的酒庄，则输出 ‘No Second Winery’，
如果没有 分数排在第三的酒庄，则输出 ‘No Third Winery’。
返回结果表按 country 升序 排列。
4、示例
输入：
Wineries 表：
| id | country | points | winery |
|---|---|---|---|
| 103 | Australia | 84 | WhisperingPines |
| 737 | Australia | 85 | GrapesGalore |
| 848 | Australia | 100 | HarmonyHill |
| 222 | Hungary | 60 | MoonlitCellars |
| 116 | USA | 47 | RoyalVines |
| 124 | USA | 45 | Eagle’sNest |
| 648 | India | 69 | SunsetVines |
| 894 | USA | 39 | RoyalVines |
| 677 | USA | 9 | PacificCrest |
输出：
| country | top_winery | second_winery | third_winery |
|---|---|---|---|
| Australia | HarmonyHill (100) | GrapesGalore (85) | WhisperingPines (84) |
| Hungary | MoonlitCellars (60) | No second winery | No third winery |
| India | SunsetVines (69) | No second winery | No third winery |
| USA | RoyalVines (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】最好的三家酒庄的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
