计算与先前记录的差异 [英] Calculating difference from previous record
问题描述
请问您在以下方面的帮助吗?
May I ask for your help with the following please ?
我正在尝试计算结果中从一条记录到下一条记录的变化.如果我向您显示我当前的查询和结果，这可能会有所帮助...
I am trying to calculate a change from one record to the next in my results. It will probably help if I show you my current query and results ...
SELECT A.AuditDate, COUNT(A.NickName) as [TAccounts],
SUM(IIF((A.CurrGBP > 100 OR A.CurrUSD > 100), 1, 0)) as [Funded]
FROM Audits A
GROUP BY A.AuditDate;
查询给了我这些结果...
The query gives me these results ...
AuditDate D/M/Y TAccounts Funded
--------------------------------------------
30/12/2011 506 285
04/01/2012 514 287
05/01/2012 514 288
06/01/2012 516 288
09/01/2012 520 289
10/01/2012 522 289
11/01/2012 523 290
12/01/2012 524 290
13/01/2012 526 291
17/01/2012 531 292
18/01/2012 532 292
19/01/2012 533 293
20/01/2012 537 295
理想情况下，我想要获得的结果类似于以下内容...
Ideally, the results I would like to get, would be similar to the following ...
AuditDate D/M/Y TAccounts TChange Funded FChange
------------------------------------------------------------------------
30/12/2011 506 0 285 0
04/01/2012 514 8 287 2
05/01/2012 514 0 288 1
06/01/2012 516 2 288 0
09/01/2012 520 4 289 1
10/01/2012 522 2 289 0
11/01/2012 523 1 290 1
12/01/2012 524 1 290 0
13/01/2012 526 2 291 1
17/01/2012 531 5 292 1
18/01/2012 532 1 292 0
19/01/2012 533 1 293 1
20/01/2012 537 4 295 2
在"17/01/2012"行中，"TChanges"的值为5，因为"TAccounts"已从以前的526增加到531."FChange"将基于"Funded"场地.我猜想要知道的是，此示例的上一行日期为'13/01/2012'.我的意思是，有些日子我没有数据(例如，周末).
Looking at the row for '17/01/2012', 'TChange' has a value of 5 as the 'TAccounts' has increased from previous 526 to 531. And the 'FChange' would be based on the 'Funded' field. I guess something to be aware of is the fact that the previous row to this example, is dated '13/01/2012'. What I mean is, there are some days where I have no data (for example over weekends).
我认为我需要使用SubQuery，但是我真的很难弄清楚从哪里开始.你能告诉我如何得到我需要的结果吗?
I think I need to use a SubQuery but I am really struggling to figure out where to start. Could you show me how to get the results I need please ?
我正在使用MS Access 2010
I am using MS Access 2010
非常感谢您的宝贵时间.
Many thanks for your time.
约翰尼.
推荐答案
这是您可以尝试的一种方法...
Here is one approach you could try...
SELECT B.AuditDate,B.TAccounts,
B.TAccount -
(SELECT Count(NickName) FROM Audits WHERE AuditDate=B.PrevAuditDate) as TChange,
B.Funded -
(SELECT Count(*) FROM Audits WHERE AuditDate=B.PrevAuditDate AND (CurrGBP > 100 OR CurrUSD > 100)) as FChange
FROM (
SELECT A.AuditDate,
(SELECT Count(NickName) FROM Audits WHERE AuditDate=A.AuditDate) as TAccounts,
(SELECT Count(*) FROM Audits WHERE (CurrGBP > 100 OR CurrUSD > 100)) as Funded,
(SELECT Max(AuditDate) FROM Audits WHERE AuditDate<A.AuditDate) as PrevAuditDate
FROM
(SELECT DISTINCT AuditDate FROM Audits) AS A) AS B
我不是使用Group By，而是使用子查询来获取TAccounts和资金，以及先前的审计日期，然后在主SELECT语句上使用它来再次获取TAccounts和资金，但是这次为前一个日期，以便可以对它们进行任何所需的计算.
Instead of using a Group By I've used subquerys to get both TAccounts and Funded, as well as the Previous Audit Date, which is then used on the main SELECT statement to get TAccounts and Funded again but this time for the previous date, so that any required calculation can be done against them.
但是我想这可能会很慢
这篇关于计算与先前记录的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
