带有MAX()和SUM()聚合的SQL查询

Question

在这里和其他论坛上搜索了很多其他主题后，我似乎找不到解决问题的方法。

我正在努力实现的是选择每家商店"花的钱"最多的账户。

到目前为止，我得到的是：

 SELECT MAX(s.Amount) MaxOfAmount
      , s.shopID
   FROM 
      ( SELECT SUM(OrderTotal) Amount
             , shopID
             , accountID 
          FROM Transactions 
         GROUP 
            BY shopID
             , accountID 
      ) s
  GROUP 
     BY s.shopID

这给了我每个shopID一个帐户花费的正确金额，但我看不到与之相关的accountID。我尝试在第一个选择中添加selection.accountID。但是，我还必须将selection.accountID添加到"GROUP BY"子句中，这将导致与"FROM"查询相同的记录集。

我在这里完全不知所措，所以任何帮助都很感激。

Answer 1

请尝试一下，这应该可以工作。

 SELECT selection1.shopID,accountID,Amount
  FROM (SELECT SUM(OrderTotal) as Amount, shopID, accountID FROM Transactions GROUP BY     shopID, accountID )  AS selection1
  INNER JOIN
(
    SELECT Max(selection2.Amount) AS MaxOfAmount, selection2.shopID
    FROM (SELECT SUM(OrderTotal) as Amount, shopID, accountID FROM Transactions GROUP BY shopID, accountID )  AS selection2
    GROUP BY selection2.shopID
)
MAX_AMOUNT ON
    MAX_AMOUNT.MaxOfAmount=selection1.Amount AND
    MAX_AMOUNT.shopID=selection1.shopID

Answer 2

我不确定您是否可以在一个查询中完成。或者这个查询会有些慢而且不美观。

我通过使用您的子查询创建"视图"或"#tmp_table"来解决同样的问题：

SELECT SUM(OrderTotal) as Amount, shopID, accountID INTO #shop_acc_amount FROM Transactions GROUP BY shopID, accountID
SELECT Max(selection.Amount) AS MaxOfAmount, selection.shopID,selection2.accountID
FROM #shop_acc_amount AS selection
JOIN #shop_acc_amount AS selection2 on selection2.shopID = selection.shopID AND selection2.Amount = Max(selection.Amount)
GROUP BY selection.shopID,selection2.accountID