sql - 从 NORTHWIND 数据库中获取每个国家最畅销的产品

Question

大家好，过去一天我一直在为此苦苦挣扎，我似乎无法弄明白。

我的任务是从名为 NORTHWIND 的流行开源数据库中获取每个国家/地区最畅销的产品:https://northwinddatabase.codeplex.com

我能够进入这个阶段，这是我在 SQL Server 中的代码:

--Get most sold product for each country
WITH TotalProductsSold AS 
(
    SELECT od.ProductID, SUM(od.Quantity) AS TotalSold
        FROM [Order Details] AS od
        GROUP BY od.ProductID
)
SELECT MAX(TotalProductsSold.TotalSold) AS MostSoldQuantity, s.Country --,p.ProductName
    FROM Products AS p
    INNER JOIN TotalProductsSold
    ON TotalProductsSold.ProductID = p.ProductID
    INNER JOIN Suppliers AS s
    ON s.SupplierID = p.SupplierID
    GROUP BY s.Country
    ORDER BY MostSoldQuantity DESC

这给了我以下结果:

这很好，但我想找出 MostSoldQuantity 的产品名称。

非常感谢!

P.S 我发表了评论 --p.ProductName 我认为它会起作用但它没有，如果有人能解释我为什么 GROUP BY 不会自动允许我导出行的产品名称那会很棒

Answer 1

首先，从销售产品的数量开始，按国家/地区统计，而不仅仅是按产品统计。然后对它们进行排名，只选择 RANK = 1 的任何东西。像...

WITH
    ProductQuantityByCountry AS 
(
    SELECT
       s.CountryID,
       p.ProductID,
       SUM(od.Quantity)   AS Quantity
    FROM
        [Order Details]   AS od
    INNER JOIN
        Products          AS p
            ON  p.ProductID = od.ProductID
    INNER JOIN
        Suppliers         AS s
            ON  s.SupplierID = p.SupplierID
    GROUP BY
       s.CountryID,
       p.ProductID
),
    RankedProductQuantityByCountry
AS
(
    SELECT
        RANK() OVER (PARTITION BY CountryID ORDER BY Quantity DESC)  AS countryRank,
        *
    FROM
        ProductQuantityByCountry
)
SELECT
    *
FROM
    RankedProductQuantityByCountry
WHERE
    countryRank = 1

请注意，一个国家/地区可能供应相同 数量的不同产品，因此两种产品的等级可能都为 1。查看 ROW_NUMER() 和/或 DENSE_RANK() 用于其他但与 RANK() 类似的行为。

编辑:要解释为什么 SQL 不允许您将 Product.Name 放入最终查询中，一个简单的练习是问一个问题。

在这种情况下 SQL 应该做什么？

SELECT
    MAX(TotalProductsSold.TotalSold) AS MostSoldQuantity,
    MIN(TotalProductsSold.TotalSold) AS LeastSoldQuantity,
    s.Country,
    p.ProductName
FROM
    blahblahblah
GROUP BY
    s.Country
ORDER BY
    MostSoldQuantity DESC

MIN 和 MAX 的存在使事情变得模棱两可。

您可能很清楚您想要执行按国家/地区的操作，并且该操作是选择销售额最高的产品来自那个国家的数量。但它实际上并不明确，对查询的小改动可能会对任何推断的行为产生非常困惑的后果。相反，SQL 的声明性语法对要解决的问题提供了非常清晰/明确/确定的描述。

如果 GROUP BY 子句中未提及表达式，则不能在不聚合的情况下SELECT 它。这样一来，对于含义或 SQL 引擎应该做什么就没有歧义。

通过要求您在查询的一个级别上规定获取每个产品在每个国家/地区的总销售额，然后您可以干净地陈述然后选择最高的在查询的另一个级别按国家/地区排名。

这感觉就像您最终遇到的查询比“应该”需要的时间更长。但它也会导致查询完全明确，既可以将查询编译成执行计划，也可以供将来阅读您的代码的其他编码人员使用。