我想要以下结果 [英] I want the below result

问题描述

您好专家

我有下表结果:

Hello Experts

I have the below table result:

PayDate     PayAmount     Client_ID
------------------------------------
2012-06-14     2000          1
2012-06-15     2000          1
2012-06-16     2000          1
2012-06-15     4500          2
2012-06-16     4500          2
2012-06-17     4500          2

我需要以下结果:

I need the below result:

PayDate     PayAmount      Sum
-------------------------------------
2012-06-14     2000        2000
2012-06-15     2000        4000
2012-06-16     2000        6000
2012-06-15     4500        4500
2012-06-16     4500        9000
2012-06-17     4500        13500

推荐答案

请查看示例:

Please, have a look at example:

DECLARE @tmp TABLE (PayDate DATETIME, PayAmount DECIMAL(8,2), Client_ID INT)

INSERT INTO @tmp (PayDate, PayAmount, Client_ID)
VALUES('2012-06-14', 2000.00, 1), ('2012-06-15', 2000.00, 1), ('2012-06-16', 2000.00, 1),
('2012-06-15', 4500.00, 2), ('2012-06-16', 4500.00, 2), ('2012-06-17', 4500.00, 2)


SELECT t1.RowNo AS OperationID, t1.Client_ID, t1.PayDate, t1.PayAmount, SUM(t2.PayAmount) AS Total
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY Client_ID ORDER BY PayDate) AS RowNo, Client_ID, PayDate, PayAmount
    FROM @tmp
    ) AS t1 INNER JOIN (
        SELECT ROW_NUMBER() OVER(PARTITION BY Client_ID ORDER BY PayDate) AS RowNo, Client_ID, PayDate, PayAmount
        FROM @tmp
            ) AS t2 ON t1.Client_ID = t2.Client_ID AND t1.RowNo >= t2.RowNo
--uncomment below line to add condition
--WHERE t1.Client_ID = 1
GROUP BY t1.Client_ID, t1.RowNo, t1.PayDate, t1.PayAmount

结果:

Result:

OperationID Client_ID   PayDate                    PayAmount  Total
1           1           2012-06-14 00:00:00.000    2000.00    2000.00
2           1           2012-06-15 00:00:00.000    2000.00    4000.00
3           1           2012-06-16 00:00:00.000    2000.00    6000.00
1           2           2012-06-15 00:00:00.000    4500.00    4500.00
2           2           2012-06-16 00:00:00.000    4500.00    9000.00
3           2           2012-06-17 00:00:00.000    4500.00    13500.00

就是您想要的吗?

Is that what you want?

尝试在这里查看: ^ ]-它提出了几种实现方法.

Try looking here: Calculating simple running totals in SQL Server[^] - it suggests several ways to do this.

请尝试执行以下操作，但我尚未经过测试，但这可能是会更正.

Please try following , I am not tested but may be this will correct .

Select A.PayDate,
          A.PayAmount,
  (Select  sum(PayAmount) as [Sum]
            From Teflan.dbo.LoanInstallment as B
              Where A.PayDate < B.PayDate
          )
  From Teflan.dbo.LoanInstallment As A
  Where Client_ID = 1
  Order By A.PayDate

这篇关于我想要以下结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！