计算每一行到下一行的列值差异 [英] Calculating difference in column value from one row to the next

问题描述

我正在使用Access DB来跟踪数百个帐户的实用程序使用情况.这些帐户中的仪表仅具有每月的消耗值.我需要获取本月的消费值，并将其从前一个月的值中减去.得到这个月的消费.我知道在SQL Server中，有一个超前/滞后函数可以计算出这些差异.访问中是否有类似的功能?还是有一种简单的方法可以从上面的一行中减去一行中的值?

I am using an Access DB to keep track of Utility Usage on hundreds of accounts. The meters in these accounts have consumption values only per month. I need to take the consumption value of this month and subtract it from the value of the previous month. to get the consumption for this month. I know that in SQL Server, there is a lead/lag function that can calculate those differences. Is there a similar function in access? or is there a simple way to subtract the value in one row from the one above it?

例如.

The first Line is Billed Date
The Second Line is the Meter Reading
The Third Line is Consumption

1/26/2014
2/25/2014
3/27/2014
4/28/2014
5/26/2014
7/29/2014

0
3163
4567
5672
7065
8468

1538
1625
1404
1105
1393
1403

推荐答案

我不太了解您的一些结果，但是我想您需要类似的东西:

I do not quite get some of your results, but I think you want something like:

SELECT Meters.MeterDate, 
     Meters.MeterReading, 
    (SELECT TOP 1 MeterReading 
     FROM Meters m WHERE m.MeterDate <Meters.MeterDate 
     ORDER BY MeterDate DESC) AS LastReading, 
     [MeterReading]-Nz([LastReading],0) AS MonthResult
FROM Meters
ORDER BY Meters.MeterReading;

这篇关于计算每一行到下一行的列值差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！