sql - 根据另一列的总和选择列

Question

假设我有

SalesManagerId, SaleAmount, ProductId

我要总结 SaleAmount对于每个( SalesManagerId ， ProductId )并获取 ProductId最大 sum(SaleAmount) .

这在一个查询中是可能的吗？

例子:

1, 100, 1
1, 200, 1
1, 600, 1
1, 400, 2
2, 100, 3
3, 100, 4
3, 100, 4
2, 500, 6
3, 100, 5

结果:

1, 900, 1
2, 500, 6
3, 200, 4

Answer 1

如果您有可用的分析函数，您可以使用 RANK()
就像是:

SELECT SalesManagerId, ProductId, Total
FROM (
  SELECT SalesManagerId,
         ProductId, 
         SUM(SaleAmount) as Total,
         RANK() OVER(PARTITION BY SalesManagerId 
                     ORDER BY SUM(SaleAmount) DESC) as R
  FROM <Table name>
  GROUP BY SalesManagerId, ProductId) as InnerQuery
WHERE InnerQuery.R = 1