如何将 1 条记录数据添加到以前的数据? [英] How to add 1 records data to previous?

问题描述

我遇到了问题，比如我正在传递 accountID 并根据该 SP 选择一个人的金额详细信息，例如

i am stuck in problem like i am passing accountID and on the basis of that SP picks amount details of a person e.g.

AccountID   AccountTitle  TransactionDate Amount

1           John01        2014/11/28      20

现在，如果同一个 accountID 有第二个或更多记录，那么它应该添加以前的 e.g.如果 accountID 1 的第二条记录是 40，则金额应显示 60(这样它应该已经添加到 20 并在第二条记录中显示总数)

now if there is 2nd or more records for same accountID then it should add with previous e.g. if 2nd record for accountID 1 is 40 then amount should display 60 (such that it should be already added to 20 and display total in 2nd record)

AccountID   AccountTitle  TransactionDate Amount

    1           John01        2014/12/30    60    (in real it was 40 but it should show result after being added to 1st record)

同样适用于更多的记录

Select Payments.Accounts.AccountID, Payments.Accounts.AccountTitle, 
       Payments.Transactions.DateTime as TranasactionDateTime, 
       Payments.Transactions.Amount from Payments.Accounts
       Inner Join Payments.Accounts
       ON Payments.Accounts.AccountID = Payments.Transactions.Account_ID
       Inner Join Payments.Transactions
       where Payments.Transactions.Account_ID = 1

它浪费了我的时间，无法再解决它，所以请帮助我，

it has wasted my time and can't tackle it anymore, so please help me,

推荐答案

SQL Server 2012+ 支持累积总和(这似乎是你想要的):

SQL Server 2012+ supports cumulative sums (which seems to be what you want):

Select a.AccountID, a.AccountTitle, t.DateTime as TranasactionDateTime, 
       t.Amount,
       sum(t.Amount) over (partition by t.Account_Id order by t.DateTime) as RunningAmount    
from Payments.Accounts a Inner Join
     Payments.Transactions t
     on a.AccountID = t.Account_ID
where t.Account_ID = 1;

在早期版本的 SQL Server 中，您可以使用相关子查询或使用 cross apply 最轻松地做到这一点.

In earlier versions of SQL Server you can most easily do this with a correlated subquery or using cross apply.

我还修复了您的查询.我不知道你为什么两次加入 Accounts 表.此外，表别名使查询更易于编写和阅读.

I also fixed your query. I don't know why you were joining to the Accounts table twice. Also, table aliases make queries much easier to write and to read.

这篇关于如何将 1 条记录数据添加到以前的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！