3050. 披萨配料成本分析-CSDN博客

本文链接：https://blog.csdn.net/CYJ1844/article/details/144040312

力扣题目跳转（3050. 披萨配料成本分析 - 力扣（LeetCode））

表：Toppings

+--------------+---------+ 
| Column Name  | Type    | 
+--------------+---------+ 
| topping_name | varchar | 
| cost         | decimal |
+--------------+---------+
topping_name 是这张表的主键。
这张表的每一行包含配料名和配料的花费。

题目要求：

编写一个解决方案根据给定的配料表来计算 所有可能的 3 种配料 披萨组合的 总花费。总花费必须舍入到 2 位整数。

注意：

不要包含重复配料的披萨。例如，‘Pepperoni, Pepperoni, Onion Pizza’。
配料必须以 字母顺序 排序。例如，'Chicken, Onions, Sausage'。'Onion, Sausage, Chicken' 不会被通过。

返回结果表，以总花费降序排序，配料的组合升序排序。

返回格式如下所示。

示例 1：

输入： 
Toppings 表：
+--------------+------+
| topping_name | cost |
+--------------+------+
| Pepperoni    | 0.50 |
| Sausage      | 0.70 |
| Chicken      | 0.55 |
| Extra Cheese | 0.40 |
+--------------+------+
输出： 
+--------------------------------+------------+
| pizza                          | total_cost | 
+--------------------------------+------------+
| Chicken,Pepperoni,Sausage      | 1.75       |  
| Chicken,Extra Cheese,Sausage   | 1.65       |
| Extra Cheese,Pepperoni,Sausage | 1.60       |
| Chicken,Extra Cheese,Pepperoni | 1.45       | 
+--------------------------------+------------+
解释： 
这三种配料只有四种不同的组合：
- Chicken, Pepperoni, Sausage：总花费是 $1.75 (Chicken $0.55，Pepperoni $0.50，Sausage $0.70)。
- Chicken, Extra Cheese, Sausage：总花费是 $1.65 (Chicken $0.55，Extra Cheese $0.40，Sausage $0.70)。
- Extra Cheese, Pepperoni, Sausage：总花费是 $1.60 (Extra Cheese $0.40，Pepperoni $0.50，Sausage $0.70)。
- Chicken, Extra Cheese, Pepperoni：总花费是 $1.45 (Chicken $0.55，Extra Cheese $0.40，Pepperoni $0.50)。
输出表根据总花费降序排序。

case 1 的建表语句。

Create table if not exists Toppings(topping_name varchar(100), cost decimal(5,2))
Truncate table Toppings
insert into Toppings (topping_name, cost) values ('Pepperoni', '0.5')
insert into Toppings (topping_name, cost) values ('Sausage', '0.7')
insert into Toppings (topping_name, cost) values ('Chicken', '0.55')
insert into Toppings (topping_name, cost) values ('Extra Cheese', '0.4')

一首先我们先增加一列排序，是根据 topping_name 排序的。

select *,row_number() over (order by topping_name) as rn from toppings;

输出如下

二然后以次表为本，开始自连接。

select
        concat_ws(',',t1.topping_name,t2.topping_name,t3.topping_name) as pizza,
        round(t1.cost + t2.cost + t3.cost,2) as total_cost from
(select *,row_number() over (order by topping_name) as rn from toppings) t1,
(select *,row_number() over (order by topping_name) as rn from toppings) t2,
(select *,row_number() over (order by topping_name) as rn from toppings) t3
where t1.rn < t2.rn and t2.rn < t3.rn order by total_cost desc ,pizza;