显示产品名称和城市，该产品的销售量最大

Question

我试图得到一个查询，以显示产品销量最高的产品名称和城市。以下是我正在使用的代码：

SELECT DISTINCT 
    (s.city), 
    MAX(t.quantity), 
    p.Name 
FROM [DS715-Cameron-Erwin].dbo.Tb_Transactions AS t, 
    [DS715-Cameron-Erwin].dbo.Tb_Product AS p, 
    [DS715-Cameron-Erwin].dbo.Tb_Supplier AS s 
WHERE p.prod_id = t.prod_id 
AND s.Supp_ID = t.Supp_ID 
GROUP BY t.Prod_ID, 
     p.name, 
     s.city 
ORDER BY p.name, s.city 

这给了我每个城市中每种产品的最高销售量。

Sample Data

从截图中有每个产品（飞机，汽车，船舶......）的多条记录。我试图为购买量最高的每个产品获取单个记录。 因此，最高纪录只会显示飞机，因为大部分订单都来自那里。

Answer 1

您想使用ROW_NUMBER() OVER功能由量订购，然后选择一个与每种产品的最大数量。

SELECT 
    city, 
    quantity, 
    name 
FROM 
(

    SELECT S.city, 
      T.quantity, 
      P.name, 
      ROW_NUMBER() OVER 
       (PARTITION BY 
       P.name 
       ORDER BY t.Quantity DESC 
      ) as RowNum 
    FROM 
      Tb_Transactions T 
       INNER JOIN 
      Tb_Product P 
       ON 
       P.prod_id = T.prod_id 
       INNER JOIN 
      Tb_Supplier S 
       ON 
       S.supp_id = T.supp_id 
) a 
WHERE 
    RowNum = 1 

http://sqlfiddle.com/#!6/628458/5

Answer 2

对于这一点，我会用一个CTE（也是我将使用显式INNER JOIN语法）：

;With CTE 
As 
(
    Select 
     s.city 
     , t.quantity 
     , p.Name 
     , Row_Number Over (Partition By P.Name, s.city Order By t.Quantity Desc) as RN 
    From [DS715-Cameron-Erwin].dbo.Tb_Transactions as t 
     Inner Join [DS715-Cameron-Erwin].dbo.Tb_Product as p 
      On p.prod_id = t.prod_id 
     Inner Join [DS715-Cameron-Erwin].dbo.Tb_Supplier as s 
      On s.Supp_ID = t.Supp_ID 
) 
Select 
    city 
    , quantity 
    , Name 
From CTE 
Where RN = 1