查询运行减法

Jahid Chowdury 发表于 Dev

游艇杂烩

我正在做一个项目，我需要在其中计算所有应付款。我在查询设计中进行了查询。但是我无法进行查询以获取总金额，已支付金额，到期金额和DueAmount列值将从先前的DueAmount值中减去。这是查询：

SELECT Payment.PayDate, OrderMaster.OrderId, OrderMaster.CustomerName, OrderMaster.TotalAmount, Payment.PayAmount, OrderMaster.TotalAmount - Payment.PayAmount AS DueAmount FROM OrderMaster INNER JOIN Payment ON OrderMaster.OrderId = Payment.OrderId;

我得到这样的查询结果：

   PayDate   | OrderId  | CustomerName | TotalAmount | PayAmount | DueAmount
   -------------------------------------------------------------------------
   20/12/2014 |     1   | Someone      |   5000      |  2000      | 3000
   22/12/2014 |     1   | Someone      |   5000      |  1000      | 4000
   25/12/2014 |     1   | Someone      |   5000      |  2000      | 3000

我想要这样的查询结果：

    PayDate   | OrderId | CustomerName | TotalAmount | PayAmount | DueAmount
   -------------------------------------------------------------------------
   20/12/2014 |     1   | Someone      |   5000      |  2000      | 3000
   22/12/2014 |     1   | Someone      |   5000      |  1000      | 2000
   25/12/2014 |     1   | Someone      |   5000      |  2000      | 0

请帮助我进行确切的查询以获得此结果。谢谢。

杰基尔·侯赛因（Jakir Hossain）

You may try like this:

select myField, myField -
(select myField from myTable where myOrderField =
(select max(myOrderField) from myTable where myOrderField < T.myOrderField))
as RunningDiff
from myTable as T

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。