简单的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)
点赞
评论