如何編寫查詢以根據配對重量為比薩餅配酒

我正在處理將飲料與比薩餅配對的查詢。

目前，我能夠顯示比薩餅名稱、飲料和配對重量，即與飲料搭配的比薩餅上的配料量。從這裡開始，我希望只輸出不同披薩名稱到飲料的映射。

我不確定如何格式化查詢以僅根據 pairing_weight 返回每個比薩餅的最佳結果。

mysql> select pizza_name, beverage, COUNT(beverage) as pairing_weight
   -> from pizza_production
   ->     JOIN pizza_restrictions pr on
   ->         pizza_production.topping_name = pr.topping_name
   ->     JOIN beverage_pairing_notes on
   ->         restriction = pairing
   ->     GROUP BY pizza_name, beverage
   ->     ORDER BY pizza_name, pairing_weight DESC;
+--------------+--------------------+----------------+
| pizza_name   | beverage           | pairing_weight |
+--------------+--------------------+----------------+
| Grand Padano | Prosecco           |              2 |
| Grand Padano | Champagne          |              2 |
| Grand Padano | Riesling           |              2 |
| Grand Padano | Cava               |              2 |
| Grand Padano | Pinot Noir         |              1 |
| Grand Padano | Carlsberg beer     |              1 |
| Grand Padano | Zinfandel Rosé     |              1 |
| Grand Padano | Chardonnay         |              1 |
| Grand Padano | Chenin Blanc       |              1 |
| Grand Padano | Gewürztraminer     |              1 |
| Grand Padano | Guinness beer      |              1 |
| Grand Padano | Pinot Grigio       |              1 |
| Grand Padano | Heineken beer      |              1 |
| Grand Padano | Irn Bru            |              1 |
| Grand Padano | Malbec             |              1 |
| Grand Padano | Muscat Blanc       |              1 |
| new york     | Riesling           |              2 |
| new york     | Champagne          |              2 |
| new york     | Prosecco           |              2 |
| new york     | Pinot Noir         |              2 |
| new york     | Malbec             |              2 |
| new york     | Syrah              |              1 |
| new york     | Cava               |              1 |
| new york     | Cabernet Sauvignon |              1 |
| new york     | Chardonnay         |              1 |
| new york     | Carlsberg beer     |              1 |
| new york     | Chenin Blanc       |              1 |
| new york     | Gewürztraminer     |              1 |
| new york     | Guinness beer      |              1 |
| new york     | Muscat Blanc       |              1 |
| new york     | Heineken beer      |              1 |
| pepperoni    | Pinot Noir         |              3 |
| pepperoni    | Malbec             |              3 |
| pepperoni    | Muscat Blanc       |              2 |
| pepperoni    | Heineken beer      |              2 |
| pepperoni    | Guinness beer      |              2 |
| pepperoni    | Gewürztraminer     |              2 |
| pepperoni    | Chenin Blanc       |              2 |
| pepperoni    | Riesling           |              2 |
| pepperoni    | Carlsberg beer     |              2 |
| pepperoni    | Cabernet Sauvignon |              2 |
| pepperoni    | Chardonnay         |              1 |
| pepperoni    | Champagne          |              1 |
| pepperoni    | Prosecco           |              1 |
| pepperoni    | Cava               |              1 |
| pepperoni    | Rioja              |              1 |
| pepperoni    | Syrah              |              1 |
| pepperoni    | Zinfandel Rosé     |              1 |
| vegetarian   | Diet Coke          |              2 |
| vegetarian   | Prosecco           |              1 |
| vegetarian   | Champagne          |              1 |
| vegetarian   | Riesling           |              1 |
+--------------+--------------------+----------------+
52 rows in set (0.01 sec)

期望輸出的一個例子是

+--------------+--------------------+
| pizza_name   | beverage           |
+--------------+--------------------+
| pepperoni    | Pinot Noir         |
| new york     | Riesling           |
| Grand Padano | Prosecco           |
| vegetarian   | Diet Coke          |
+--------------+--------------------+

有沒有辦法從上面查詢輸出中已有的數據中提取這些數據？

版本：

mysql> status -------------- /usr/local/mysql/bin/mysql  Ver 8.0.23 for macos10.15 on x86_64 (MySQL Community Server - GPL)

如果每個比薩的多種飲料的pairing_weight 相同，則可以選擇其中任何一種，如果需要，它可以是完全隨機的，或者只是查詢顯示的具有最高重量的第一個。

作為經典的Greatest N per group問題，可以使用以下ROW_NUMBER()函式輕鬆解決：

SELECT
 pizza_name, beverage
FROM
 (
   select
       pizza_name, beverage,
       ROW_NUMBER() OVER (
         PARTITION BY pizza_name
         ORDER BY COUNT(beverage) DESC
       ) as pizza_beverage_ranking
   from pizza_production
       JOIN pizza_restrictions pr on
           pizza_production.topping_name = pr.topping_name
       JOIN beverage_pairing_notes on
           restriction = pairing
       GROUP BY pizza_name, beverage
 ) AS derived
WHERE
 pizza_beverage_ranking = 1
;

正如函式的ORDER BY子句中所指定的ROW_NUMBER()，排名是按 的降序分配的COUNT(beverage)。然而，除此之外，順序是任意的，當兩行或多行具有相同的權重時，哪一行的排名較低或較高將取決於伺服器。COUNT(beverage) DESC如果您希望訂單更具體，您可以在之後添加更多標準，從而使結果更可預測。除了排序之外，還有一個子句，它還指定對共享相同值PARTITION BY的每組行分別進行列舉。pizza_name

一旦分配了行號，您就可以對其進行過濾，正如您在外部 SELECTWHERE子句中看到的那樣。

作為旁注，請考慮始終使用表名或別名（例如，pizza_production.pizza_name而不是pizza_name）來限定列，其中查詢涉及多個表。雖然如果沒有衝突，伺服器當然可以解決不合格的列，但如果列是明確限定的，那麼人類更容易閱讀和理解查詢，特別是當讀者對模式不是很熟悉時。這對於您提供給未知受眾進行研究的查詢是正確的，對於您曾經自己編寫並忘記但由於某種原因不得不返回的查詢也是如此。

引用自：https://dba.stackexchange.com/questions/290774