Question

3050. Pizza Toppings Cost Analysis

English Version

题目描述

Table: Toppings

+--------------+---------+ 
| Column Name  | Type  | 
+--------------+---------+ 
| topping_name | varchar | 
| cost     | decimal |
+--------------+---------+
topping_name is the primary key for this table.
Each row of this table contains topping name and the cost of the topping. 

Write a solution to calculate the total cost of all possible 3-topping pizza combinations from a given list of toppings. The total cost of toppings must be rounded to 2 decimal places.

Note:

• Do not include the pizzas where a topping is repeated. For example, ‘Pepperoni, Pepperoni, Onion Pizza’.
• Toppings must be listed in alphabetical order. For example, 'Chicken, Onions, Sausage'. 'Onion, Sausage, Chicken' is not acceptable.

Return_ the result table ordered by total cost in_ _descending_ _order and combination of toppings in ascending order._

The result format is in the following example.

 

Example 1:

Input: 
Toppings table:
+--------------+------+
| topping_name | cost |
+--------------+------+
| Pepperoni  | 0.50 |
| Sausage    | 0.70 |
| Chicken    | 0.55 |
| Extra Cheese | 0.40 |
+--------------+------+
Output: 
+--------------------------------+------------+
| 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     | 
+--------------------------------+------------+
Explanation: 
There are only four different combinations possible with the three topings:
- Chicken, Pepperoni, Sausage: Total cost is $1.75 (Chicken $0.55, Pepperoni $0.50, Sausage $0.70).
- Chicken, Extra Cheese, Sausage: Total cost is $1.65 (Chicken $0.55, Extra Cheese $0.40, Sausage $0.70).
- Extra Cheese, Pepperoni, Sausage: Total cost is $1.60 (Extra Cheese $0.40, Pepperoni $0.50, Sausage $0.70).
- Chicken, Extra Cheese, Pepperoni: Total cost is $1.45 (Chicken $0.55, Extra Cheese $0.40, Pepperoni $0.50).
Output table is ordered by the total cost in descending order.

解法

方法一：窗口函数 + 条件连接

我们先使用窗口函数，按照 topping_name 字段对表进行排序，并为每一行添加一个 rk 字段，表示当前行的排名。

然后我们使用条件连接，连接三次表 T，分别为 t1, t2, t3。连接条件是 t1.rk < t2.rk 和 t2.rk < t3.rk。然后我们计算三个配料的总价，按照总价降序排序，再按照配料名升序排序。

# Write your MySQL query statement below
WITH
  T AS (
    SELECT *, RANK() OVER (ORDER BY topping_name) AS rk
    FROM Toppings
  )
SELECT
  CONCAT(t1.topping_name, ',', t2.topping_name, ',', t3.topping_name) AS pizza,
  t1.cost + t2.cost + t3.cost AS total_cost
FROM
  T AS t1
  JOIN T AS t2 ON t1.rk < t2.rk
  JOIN T AS t3 ON t2.rk < t3.rk
ORDER BY 2 DESC, 1 ASC;