显示总和的最大值

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

只需在外部选择中按 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