sql - MS SQL 如何根据最旧日期查询金额

Question

我正在连接两个表:tblAccount 和 tblInvoice。 TblAccount 有多个与每个帐户相关的发票。我正在尝试根据最旧的 InvoiceDate 查询 TotalSavings 金额，如果同一日期有两个金额值，那么我需要将这两个金额相加。我认为我这样做是正确的，但下面的代码没有给我所需的输出。

SELECT TA.AccountID,
MIN(TI.InvoiceDate) AS InvoiceDate,
TI.TotalSavings
FROM tblAccount AS TA
LEFT JOIN tblInvoice AS TI
ON TA.AccountID=TI.AccountID
WHERE TI.TotalSavings>0
AND
TI.InvoiceDate IS NOT NULL
GROUP BY TA.AccountID, TI.TotalSavings
ORDER BY TA.AccountID

当我连接两个表时，我得到以下结果。

AccountID   Invoice Date    TotalSavings
ABC 6/30/2012   10
ABC 12/31/2013  20
ABC 6/1/2014    30
BCA 9/30/2011   40
BCA 1/31/2012   50
BCA 11/30/2011  60
CBA 3/1/2015    70
CBA 3/1/2015    80

我正在寻找这样的输出

AccountID   Invoice Date    TotalSavings
ABC 6/30/2012   10
BCA 9/30/2011   40
CBA 3/1/2015    150

如果您能在此论坛上提供任何帮助，我将不胜感激。

Answer 1

将连接与子查询结合使用

select a.accountid, s.invoicedate, 
       sum(s.TotalSavings) as TotalSavings
from tblAccount a 
left join tblInvoice s on s.accountid = a.accountid 
where s.invoicedate is not null and s.TotalSavings > 0 and
      s.invoicedate = (select min(invoicedate) 
                       from tblInvoice 
                       where accountid = a.accountid)
group by a.accountid, s.invoicedate;

您还可以仅使用 exists 来切换 join

select accountid, invoicedate, sum(TotalSavings) as TotalSavings
from tblInvoice s 
where exists (select 1 from tblAccount where accountid  = s.accountid) and 
TotalSavings > 0 and
invoicedate = (select min(invoicedate) from tblInvoice where accountid  = s.accountid)
group by accountid, invoicedate