简单的SQL查询-显示每个卖家的最大销售

早上好,我有一个简单的表格,例如:

Name_Seller Month Value

卖方A一月200

卖方B一月100

卖方A Fev 300

卖方B Fev 100

卖方C一月400

卖方A Mar 200

卖方D一月300

   SELECT Name_Seller, Month, Value FROM SALES
WHERE Value = (Select MAX(Value) FROM SALES GROUP BY Name_Seller);

而且我想为每个卖方打印这是他的最大销售额以及何时。

我正在尝试类似上面的查询之类的方法,但是没有用...

您能帮我解决它,或者向我解释为什么我的查询不起作用吗?

谢谢!

评论
  • Oo珑玲
    Oo珑玲 回复

    它看起来像这样:

    SELECT Name_Seller, Month, MAX(Value)
    FROM SALES
    GROUP BY Name_Seller, Month;
    
  • 挂机
    挂机 回复

    , 你好

    您可以使用以下查询,

    select name_seller, month, max(value) from sales group by name_seller, month;
    

    如果您也希望月份,请使用,

    select s2.name_seller, s1.month, max(s2.value) from sales s1
    inner join 
    (select name_seller, max(value) as value from sales
    group by name_seller) s2
    on (s1.name_seller = s2.name_seller and s1.value = s2.value);
    
  • 淡淡的思绪
    淡淡的思绪 回复

    With ROW_NUMBER() window function:

    SELECT t.Name_Seller, t.Month, t.Value
    FROM (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY Name_Seller ORDER BY Value DESC) rn
      FROM SALES
    ) t
    WHERE t.rn = 1
    

    Change ROW_NUMBER() with RANK() if you want ties returned.

    Or with a correlated subquery in the WHERE clause:

    SELECT s.* FROM SALES s
    WHERE s.Value = (SELECT MAX(VALUE) FROM SALES WHERE Name_Seller = s.Name_Seller)