根据另一列的 max() 选择列

Question

鉴于下面两个表格案例和acct_transaction的数据，我如何只包含最大acct_transaction金额的 acct_transaction.create_date，同时计算所有金额的总和和最大金额的值？ 平台是t-sql。

id  amount     create_date  
---|----------|------------|
1  | 1.99     | 01/09/2009 | 
1  | 2.99     | 01/13/2009 | 
1  | 578.23   | 11/03/2007 | 
1  | 64.57    | 03/03/2008 | 
1  | 3.99     | 12/12/2012 | 
1  | 31337.00 | 04/18/2009 | 
1  | 123.45   | 05/12/2008 | 
1  | 987.65   | 10/10/2010 | 

结果集应如下所示：

id  amount     create_date  sum        max_amount  max_amount_date
---|----------|------------|----------|-----------|-----------
1  | 1.99     | 01/09/2009 | 33099.87 | 31337.00  | 04/18/2009
1  | 2.99     | 01/13/2009 | 33099.87 | 31337.00  | 04/18/2009
1  | 578.23   | 11/03/2007 | 33099.87 | 31337.00  | 04/18/2009
1  | 64.57    | 03/03/2008 | 33099.87 | 31337.00  | 04/18/2009
1  | 3.99     | 12/12/2012 | 33099.87 | 31337.00  | 04/18/2009
1  | 31337.00 | 04/18/2009 | 33099.87 | 31337.00  | 04/18/2009
1  | 123.45   | 05/12/2008 | 33099.87 | 31337.00  | 04/18/2009
1  | 987.65   | 10/10/2010 | 33099.87 | 31337.00  | 04/18/2009

这就是我目前所拥有的，我只是不知道如何为max_amount_date列提取最大acct_transaction金额的日期。

SELECT      cases.id,  acct_transaction.amount, acct_transaction.create_date AS 'create_date',  SUM(acct_transaction.amount) OVER () AS 'sum', MIN(acct_transaction.amount) OVER () AS 'max_amount'
FROM        cases INNER JOIN
            acct_transaction ON cases.id = acct_transaction.id
WHERE       (cases.id = '1') 

Answer 1

;WITH x AS 
(
  SELECT c.id, t.amount, t.create_date, 
    s = SUM(t.amount) OVER(), 
    m = MAX(t.amount) OVER(),
    rn = ROW_NUMBER() OVER(ORDER BY t.amount DESC)
   FROM dbo.cases AS c
   INNER JOIN dbo.acct_transaction AS t
   ON c.id = t.id
)
SELECT x.id, x.amount, x.create_date,
   [sum] = y.s,
   max_amount = y.m, 
   max_amount_date = y.create_date
FROM x CROSS JOIN x AS y WHERE y.rn = 1;

Answer 2

您可以对定义聚合的表进行完整的外部连接：

select id, amount, create_date, x.sum, x.max_amount, x.max_amount_date
from table1
full outer join 
(select sum(amount) as sum, max(amount) as max_amount, 
   (select top 1 create_date from table1 where amount = (select max(amount) from table1)) as max_amount_date
 from table1) x
on 1 = 1 

SQL 小提琴演示

Answer 3

试试这个可憎的查询...我不要求它的速度或优雅。我很可能应该祈祷鳕鱼怜悯我的灵魂。

下面是您提到的两个表上的联接输出，但您没有为其提供架构。

[SQL Fiddle][1]
SELECT A.case_id
 ,A.trans_id
 ,A.trans_amount
 ,A.trans_create_date
 ,A.trans_type
 ,B.max_amount
 ,B.max_amount_date
 ,E.sum_amount
FROM acct_transaction AS A
INNER JOIN (select C.case_id
 ,MAX(C.trans_amount) AS max_amount
 ,C.trans_create_date AS max_amount_date 
 FROM acct_transaction AS C group by C.case_id, C.trans_create_date ) AS B ON B.case_id = A.case_id
inner JOIN (select D.case_id, SUM(D.trans_amount) AS sum_amount FROM acct_transaction AS D GROUP BY D.case_id) AS E on E.case_id = A.case_id
WHERE (A.case_id = '1') AND (A.trans_type = 'F')
GROUP BY A.case_id

Answer 4

谢谢，这让我走上了正确的轨道，这是有效的：

,CAST((SELECT TOP 1 t2.create_date from acct_transaction t2 
            WHERE t2.case_sk = act.case_sk AND (t2.trans_type = 'F')
            order by t2.amount, t2.create_date DESC) AS date) AS 'max_date'

它不会让我投票，因为我的:(少于 15 次代表