显示和的最大值

Question

我试图找到求和结果的最大值，但我也找不到一种方法来显示日期。我有一个transactions表，其中的数据如下：

transaction_date--Transaction_agent_id--transaction_amount
2/5/11                         321      20
2/5/11                         321      10
2/5/11                         400      100
3/5/11                         321      5
3/5/11                         345      100
3/5/11                         321      10
3/5/11                         345      50
4/5/11                         345      80
4/5/11                         321      5

我想要的结果是这样的

transaction_date--Transaction_agent_id--transaction_amount
    2/5/11                       321     30
    2/5/11                       400    100
    3/5/11                       345    150

它应该做的是首先将每个agent_id每天的所有金额相加，然后向我显示该代理最好的一天。

我所做的是这样的

SELECT a.transaction_agent_id, max(a.stotal) FROM
(
SELECT transaction_date,transaction_agent_id, sum(transaction_amount) as 'Stotal' FROM transactions
GROUP BY transaction_agent_id, transaction_date
)a
GROUP BY a.transaction_agent_id

这就给了我这个

Transaction_agent_id--transaction_amount
               321     30
               400    100
               345    150

这是正确的，但我不能添加transaction_date。

更新:我刚刚解决了这个问题。好吧，如果有人想要解决方案，那就在这里。

SELECT  a.transaction_date, a.transaction_agent_id, max(a.stotal) FROM
(
  SELECT row_number() over (partition by transaction_agent_id order by sum(transaction_amount)desc) AS 'roww' ,transaction_date,transaction_agent_id, sum(transaction_amount) as 'Stotal' FROM transactions
  GROUP BY transaction_agent_id, transaction_date
) a

WHERE a.roww = 1
GROUP BY a.transaction_date, a.transaction_agent_id, a.roww

感谢你们所有人的投入。

Answer 1

SQL语句

;WITH Max_Transaction_Amount AS (
    SELECT  Transaction_Date
            , Transaction_Agent_ID
            , Transaction_Amount = SUM(Transaction_Amount)
    FROM    q
    GROUP BY
            Transaction_Date
            , Transaction_Agent_ID
)
SELECT  mta.Transaction_Date
        , mta.Transaction_Agent_ID
        , mta.Transaction_Amount
FROM    Max_Transaction_Amount mta 
        INNER JOIN (
            SELECT  Transaction_Agent_ID
                    , Transaction_Amount = MAX(Transaction_Amount)
            FROM    Max_Transaction_Amount
            GROUP BY
                    Transaction_Agent_ID        
        ) q ON  mta.Transaction_Agent_ID = q.Transaction_Agent_ID
                AND mta.Transaction_Amount = q.Transaction_Amount

测试脚本

/* Create test data */
;WITH q (Transaction_Date, Transaction_Agent_ID, Transaction_Amount) AS (
  SELECT '2/5/11',321,20
  UNION ALL SELECT '2/5/11',321,10
  UNION ALL SELECT '2/5/11',400,100
  UNION ALL SELECT '3/5/11',321,5
  UNION ALL SELECT '3/5/11',345,100
  UNION ALL SELECT '3/5/11',321,10
  UNION ALL SELECT '3/5/11',345,50
  UNION ALL SELECT '4/5/11',345,80
  UNION ALL SELECT '4/5/11',321,5
)
/* Actual query */
, Max_Transaction_Amount AS (
    SELECT  Transaction_Date
            , Transaction_Agent_ID
            , Transaction_Amount = SUM(Transaction_Amount)
    FROM    q
    GROUP BY
            Transaction_Date
            , Transaction_Agent_ID
)
SELECT  mta.Transaction_Date
        , mta.Transaction_Agent_ID
        , mta.Transaction_Amount
FROM    Max_Transaction_Amount mta 
        INNER JOIN (
            SELECT  Transaction_Agent_ID
                    , Transaction_Amount = MAX(Transaction_Amount)
            FROM    Max_Transaction_Amount
            GROUP BY
                    Transaction_Agent_ID        
        ) q ON  mta.Transaction_Agent_ID = q.Transaction_Agent_ID
                AND mta.Transaction_Amount = q.Transaction_Amount

Answer 2

也只需在外部select中按transaction_date分组：

SELECT a.transaction_date, a.transaction_agent_id, max(a.stotal) FROM
(
  SELECT transaction_date,transaction_agent_id, sum(transaction_amount) as 'Stotal' FROM transactions
  GROUP BY transaction_agent_id, transaction_date
) a
GROUP BY a.transaction_agent_id, a.transaction_date