多表取最大值去重计数：菜谱分数排序SQL查询修复

Let's break down how to solve this SQL problem where we need to calculate recipe scores correctly—summing the maximum price weight for each associated flyer item, without missing ingredients or counting the same flyer item multiple times.

Understanding the Requirements #

We need to retrieve recipe_id and its total score, where:

• The score is the sum of the maximum price weight for each flyer item linked to the recipe's ingredients.
• Even if multiple ingredients from the same recipe map to the same flyer item, we only count that flyer item's max price weight once.

The Core Problem with Previous Queries #

• Either some ingredients/flyer items were omitted (leading to undercalculated scores)
• Or the same flyer item was counted multiple times (once per ingredient in the item) leading to overcalculated scores.

Solution Query #

Here's a query that addresses both issues:

SELECT 
    recipe_flyer_items.recipe_id,
    SUM(fi_max.max_price_weight) AS total_score
FROM (
    -- First, get all unique flyer items linked to each recipe
    SELECT DISTINCT
        itr.recipe_id,
        ifi.flyer_item_id
    FROM ingredient_to_recipe itr
    JOIN ingredient_to_flyer_item ifi 
        ON itr.ingredient_id = ifi.ingredient_id
) recipe_flyer_items
JOIN (
    -- Calculate the max price weight for each flyer item once
    SELECT 
        flyer_item_id,
        MAX(price_weight) AS max_price_weight
    FROM ingredient_to_flyer_item
    GROUP BY flyer_item_id
) fi_max 
    ON recipe_flyer_items.flyer_item_id = fi_max.flyer_item_id
GROUP BY recipe_flyer_items.recipe_id
ORDER BY total_score DESC;

How This Works #

1. Unique Flyer Items per Recipe: The subquery recipe_flyer_items uses DISTINCT to ensure each flyer item is only listed once per recipe, even if multiple ingredients in the recipe belong to that flyer item. This eliminates duplicate counts.
2. Precompute Max Price Weight: The subquery fi_max calculates the highest price weight for each flyer item once, so we don't recalculate it every time we link to an ingredient.
3. Sum and Sort: Finally, we join these two results, sum the max price weights per recipe, and sort by score (descending, as requested).

Alternative Approach (Using Double Group By) #

If you prefer avoiding DISTINCT, you can use two levels of grouping:

SELECT 
    itr.recipe_id,
    SUM(fi_max.max_price_weight) AS total_score
FROM ingredient_to_recipe itr
JOIN ingredient_to_flyer_item ifi 
    ON itr.ingredient_id = ifi.ingredient_id
JOIN (
    SELECT 
        flyer_item_id,
        MAX(price_weight) AS max_price_weight
    FROM ingredient_to_flyer_item
    GROUP BY flyer_item_id
) fi_max 
    ON ifi.flyer_item_id = fi_max.flyer_item_id
-- First group by recipe and flyer item to avoid duplicates
GROUP BY itr.recipe_id, fi_max.flyer_item_id
-- Then group by recipe to sum the unique flyer item weights
GROUP BY itr.recipe_id
ORDER BY total_score DESC;

This works by first grouping at the recipe + flyer item level (ensuring each flyer item is counted once per recipe), then grouping again to sum those unique values.

Both queries will correctly calculate the score without omissions or duplicates.

内容的提问来源于stack exchange，提问作者Jeff B.