sql - 选择具有最新日期的帐号

Question

我已经尝试解决这个问题好几天了。我有一个名为 Stat 的表，具有以下简化结构和示例数据:

Customer    BankID  AccNumb Type                Date        Amount    AccType

Customer 1  Boa     5       Account Statement   2015-01-01  10000,00    Eur
Customer 1  CS      10      Account Statement   2015-04-04  22000,00    Eur
Customer 2  Sa      15      Account Statement   2015-03-13  3000,00     Eur
Customer 2  Sa      40      Account Statement   2015-04-24  1000,00     Eur
Customer 2  Sa      15      Sale Advice         2015-04-16  400,00      Eur
Customer 2  Sa      15      Account Statement   2015-12-24  50,00       Usd
Customer 2  Boa     20      Sale Advice         2015-05-15  6000,00     Eur
Customer 3  Cu      25      Account Statement   2015-11-27  81000,00    Eur
Customer 3  Cu      30      Sale Advice         2015-11-27  3000,00     Usd
Customer 3  Pop     30      Account Statement   2015-11-27  12000,00    Eur

我想要做的是选择指定了最新日期的 AccountNumber。客户还可以在不同的银行拥有不同的帐号，因此也应该按 BankID 和 Customer 进行分组。

我已经走到这一步了:

SELECT AccNumb, Customer, BankID,
(SELECT TOP 1 Amount FROM Stat 
        WHERE AccNumb = y.AccNumb AND Customer = y.Customer AND 
              BankID = y.BankID AND Type = 'Account Statement' AND 
              Date = MAX(y.Date) GROUP BY Amount) Amount
FROM Stat y
    GROUP BY AccNumb, Customer, BankID
        ORDER BY Customer, AccNumb

它工作正常，问题是我还应该添加 AccType 和 Date 列我设法用另外两个子选择来做到这一点(查询需要很长时间，但它有效)。

但现在我遇到的问题是客户(或日期)列中也有 NULL 值。现在，如果是最新日期，这些“NULL”客户的帐号仍然应该显示。我也尝试通过单独加入表来执行相同的操作，但没有成功。

SELECT x.AccNumber, x.Customer, x.BankID, x.Date, y.Amount, y.AccType
FROM Stat y RIGHT JOIN
    (SELECT AccNumber, Customer, BankID, MAX(Date) Date  FROM Stat 
        GROUP BY AccNumber, Customer, BankID) x 
 ON x.AccNumber = y.AccNumber AND 
    x.Customer = y.Customer AND 
    x.BankID = y.BankID AND 
    x.Date = y.Date
ORDER BY y.Customer, y.AccNumber        

但现在“NULL”客户的“金额”、“日期”和“AccType”列中只有 NULL 值，这是不正确的。

输出应该是这样的

AccNumb     Customer        BankID  Amount  Date        AccType

111111111   a               Boa     1234.40 31.06.2014  Eur
222222222   NULL            Boa     5678.40 31.04.2014  Eur
333333333   b               Boa     0.00    25.02.2014  Eur
444444444   NULL            Boa     9101.40 23.04.2015  Eur
555555555   NULL            Boa     1213.40 31.02.2014  Usd
A66666666   c               Sa      NULL    31.02.2014  Eur
777777777   c               Sa      1415.00 31.12.2014  Eur
888888888   c               Boa     1617.40 31.12.2014  Usd
999999999   f               Pop     5678.64 31.10.2014  Eur

提前致谢。

Answer 1

只要使用row_number()，如果我理解正确的话:

select s.*
from (select s.*,
             row_number() over (partition by customer, bankId order by date desc) as seqnum
      from stat s
     ) s
where seqnum = 1;