How to add 1 records data to previous?(如何将 1 条记录数据添加到以前的数据?)
问题描述
我遇到了问题,比如我正在传递 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 条记录数据添加到以前的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:如何将 1 条记录数据添加到以前的数据?
基础教程推荐
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01