我几天来一直在尝试解决这个问题。我有一个名为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和客户分组。
我走了这么远:
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我设法通过另外2个子选择来做到这一点(查询需要很长时间,但它可以工作)。
但是现在我有一个问题,在“客户”(或“日期”)列中也有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'客户在Amount,Date和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
提前致谢。
只要使用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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句