SQL新手求助：按国家分组查询销量最高的产品

你当前的查询是把每种产品的总销量作为单独列返回，要得到每个国家销量最高的产品，需要先把这些列转换成行（也就是“逆透视”），再找出每个国家销量最大的产品。

方法一：通用SQL写法（适用于所有数据库） #

这个方法用UNION ALL把各产品的销量数据合并成统一的行结构，再用窗口函数筛选出每个国家的Top1产品：

WITH country_product_sales AS (
    -- 把原查询的列转成国家、产品、总销量的行格式
    SELECT Country, 'Liquids' AS Product, SUM(Liquids) AS TotalSales
    FROM marketing_data
    GROUP BY Country
    UNION ALL
    SELECT Country, 'Veg' AS Product, SUM(Veg) AS TotalSales
    FROM marketing_data
    GROUP BY Country
    UNION ALL
    SELECT Country, 'NonVeg' AS Product, SUM(NonVeg) AS TotalSales
    FROM marketing_data
    GROUP BY Country
    UNION ALL
    SELECT Country, 'Fish' AS Product, SUM(Fish) AS TotalSales
    FROM marketing_data
    GROUP BY Country
    UNION ALL
    SELECT Country, 'Chocolates' AS Product, SUM(Chocolates) AS TotalSales
    FROM marketing_data
    GROUP BY Country
    UNION ALL
    SELECT Country, 'Commodities' AS Product, SUM(Commodities) AS TotalSales
    FROM marketing_data
    GROUP BY Country
),
ranked_products AS (
    -- 对每个国家的产品按销量降序排名
    SELECT 
        Country,
        Product,
        TotalSales,
        -- 用ROW_NUMBER()会给并列销量的产品分配不同排名，只取第一个；用RANK()会保留并列的多个Top1
        ROW_NUMBER() OVER (PARTITION BY Country ORDER BY TotalSales DESC) AS rn
    FROM country_product_sales
)
-- 筛选出每个国家排名第一的产品
SELECT Country, Product AS "Most popular product"
FROM ranked_products
WHERE rn = 1;

关键说明： #

• UNION ALL：把每种产品的销量统计结果合并成一个数据集，每行对应一个国家的一种产品和它的总销量。
• ROW_NUMBER() OVER (PARTITION BY Country ORDER BY TotalSales DESC)：按国家分组，每组内按销量从高到低给产品排名，每个国家的销量最高产品会得到排名1。
• 如果想保留同国家销量并列第一的多个产品，把ROW_NUMBER()换成RANK()即可。

方法二：用UNPIVOT简化（适用于SQL Server、Oracle等支持UNPIVOT的数据库） #

如果你的数据库支持UNPIVOT语法，可以简化列转行的步骤：

WITH country_product_sales AS (
    -- 先按国家统计各产品总销量，再用UNPIVOT转成行
    SELECT Country, Product, TotalSales
    FROM (
        SELECT 
            Country,
            SUM(Liquids) AS TotalLiquids,
            SUM(Veg) AS TotalVeg,
            SUM(NonVeg) AS TotalNonVeg,
            SUM(Fish) AS TotalFish,
            SUM(Chocolates) AS TotalChocolates,
            SUM(Commodities) AS TotalCommodities
        FROM marketing_data
        GROUP BY Country
    ) AS src
    UNPIVOT (
        TotalSales FOR Product IN (
            TotalLiquids, TotalVeg, TotalNonVeg, TotalFish, TotalChocolates, TotalCommodities
        )
    ) AS unpvt
),
ranked_products AS (
    SELECT 
        Country,
        Product,
        REPLACE(Product, 'Total', '') AS "Most popular product", -- 去掉Product列的Total前缀
        ROW_NUMBER() OVER (PARTITION BY Country ORDER BY TotalSales DESC) AS rn
    FROM country_product_sales
)
SELECT Country, "Most popular product"
FROM ranked_products
WHERE rn = 1;

关键说明： #

• UNPIVOT直接把原查询的多列（TotalLiquids等）转换成Product和TotalSales两列，比UNION ALL更简洁。
• REPLACE(Product, 'Total', '')是把TotalLiquids这类名称转换成你想要的Liquids格式。

内容的提问来源于stack exchange，提问作者Timothy Wells