SQL技术求助：如何获取右列Max值对应左列属性及按Product ID查销量最高国家

Hey there! Let's tackle your SQL problem—since you're new to this, I'll make sure to explain things clearly so you understand not just the code, but why it works.

For each Product ID, return the country where that product had the highest sales volume. If multiple countries tie for the top spot, we just need to pick one (no need to return all ties).

Most modern SQL databases (like PostgreSQL, MySQL 8+, SQL Server, etc.) support window functions, which are perfect for this scenario because they let you rank rows within groups (in this case, groups of the same product_id).

Here's the code, assuming your table is named sales with columns product_id, country, and sales_volume:

WITH ranked_sales AS (
  SELECT
    product_id,
    country,
    sales_volume,
    -- Assign a rank to each row per product, sorted by sales volume descending
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_volume DESC) AS sales_rank
  FROM sales
)
SELECT product_id, country
FROM ranked_sales
WHERE sales_rank = 1;

How this works: #

• PARTITION BY product_id: Groups rows by each unique product ID.
• ORDER BY sales_volume DESC: Sorts each group from highest to lowest sales.
• ROW_NUMBER(): Assigns a unique number to each row in the group. The highest sales row gets sales_rank = 1.
• The final query filters to only keep rows where sales_rank = 1—giving you the top-selling country per product.

Note: If multiple countries have the same max sales for a product, ROW_NUMBER() will randomly assign one of them as rank 1 (the exact order depends on your database's default sorting). If you ever want to return all tied countries instead, replace ROW_NUMBER() with RANK()—this will assign sales_rank = 1 to all rows with the max sales.

If you're working with an older database that doesn't support window functions (like MySQL 5.x), you can use a subquery to first find the maximum sales per product, then join back to the original table to get the corresponding country.

SELECT s.product_id, s.country
FROM sales s
INNER JOIN (
  -- First, get the max sales volume for each product
  SELECT product_id, MAX(sales_volume) AS max_sales
  FROM sales
  GROUP BY product_id
) max_sales_data 
  ON s.product_id = max_sales_data.product_id 
  AND s.sales_volume = max_sales_data.max_sales
-- To pick only one country per product (if there are ties), add one of these:
-- For MySQL: GROUP BY s.product_id
-- For PostgreSQL: DISTINCT ON (s.product_id)
-- For SQL Server: TOP 1 WITH TIES ORDER BY s.sales_volume DESC

How this works: #

1. The subquery calculates the highest sales volume for each product.
2. We join this result back to the original sales table to find all rows where the product ID matches and the sales volume equals the max value.
3. The optional final line ensures we only get one country per product even if there are ties.

Your core question boils down to: "How do I get the associated attribute (like country) that corresponds to the maximum value (like sales volume) for each group?"

The key here is that MAX() is an aggregate function—it only returns the highest number, not the entire row that contains that number. To get the associated attribute, you need to:

1. Find the maximum value per group (using GROUP BY or window functions).
2. Match that value back to the original row that has it (using a join or filter on the ranked rows).

The two solutions above both do this—window functions are just more concise and flexible for these kinds of "top N per group" problems.

内容的提问来源于stack exchange，提问作者Liav shabtai