sql-server - 根据先前和当前行值计算值
问题描述
我正在计算可以通过当前行值和前一行值获得的值的帐户数据。我的问题是我必须到达两列,两列都是相互链接的,两列都是通过当前行值和前一行值获得的。
我的样本数据
create Table Accounts (SNo int,Groups varchar(10),ToCollect int,Collected int)
insert into Accounts values(1, 'A', 4748, 4650)
insert into Accounts values(2, 'A', 4643, 4582)
insert into Accounts values(3, 'A', 4582, 4482)
insert into Accounts values(4, 'A', 4482, 4415)
insert into Accounts values(5, 'A', 4415, 4330)
insert into Accounts values(6, 'A', 4332, 4241)
insert into Accounts values(7, 'A', 4241, 2246)
insert into Accounts values(8, 'A', 4166, 6000)
insert into Accounts values(9, 'A', 4080, 0)
尝试查询:
Select *,case when ToCollect>Collected then Collected Else ToCollect End Adjusted
,case when Collected>ToCollect then Collected-ToCollect Else 0 End Excess into #1
from Accounts
select a.SNo,a.Groups,a.ToCollect,a.Collected
,case when a.ToCollect>(a.Collected+b.Excess) then a.Collected+b.Excess Else a.ToCollect End Adjusted
,case when a.Collected+b.Excess>a.ToCollect then a.Collected+b.Adjusted-a.ToCollect Else 0 End Excess
from #1 a left join #1 b on a.SNo=b.SNo-1
实际结果:
SNo Groups ToCollect Collected Adjusted Excess
1 A 4748 4650 4650 0
2 A 4643 4582 4582 0
3 A 4582 4482 4482 0
4 A 4482 4415 4415 0
5 A 4415 4330 4330 0
6 A 4332 4241 4241 0
7 A 4241 2246 4080 0
8 A 4166 6000 4166 1834
9 A 4080 0 4080 0
预期结果:
SNo Groups ToCollect Collected Adjusted Balance Excess
1 A 4748 4650 4650 98 0
2 A 4643 4582 4582 61 0
3 A 4582 4482 4482 100 0
4 A 4482 4415 4415 67 0
5 A 4415 4330 4330 85 0
6 A 4332 4241 4241 91 0
7 A 4241 2246 2246 1995 0
8 A 4166 6000 4166 0 1834
9 A 4080 0 1834 2246 0
解决方案
这得到了 OP 想要的结果,但不知道这是否真的是正确的答案:
WITH CTE AS(
SELECT A.SNo,
A.Groups,
A.ToCollect,
A.Collected,
CASE WHEN A.ToCollect > A.Collected THEN A.Collected ELSE A.ToCollect END AS Adjusted,
CASE WHEN A.ToCollect > A.Collected THEN A.ToCollect - A.Collected ELSE 0 END AS Balance,
CASE WHEN A.ToCollect < A.Collected THEN A.Collected - A.ToCollect ELSE 0 END AS Excess,
SUM(CASE WHEN A.ToCollect < A.Collected THEN A.Collected - A.ToCollect ELSE 0 END) OVER (ORDER BY SNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS RunningExcess
FROM dbo.Accounts A)
SELECT C.SNo,
C.Groups,
C.ToCollect,
C.Collected,
ISNULL(NULLIF(C.RunningExcess,0),C.Adjusted) AS Adjusted,
C.Balance - ISNULL(C.RunningExcess,0) AS Balance,
C.Excess
FROM CTE C;
推荐阅读
- node.js - 获取字段与 MongoDB + node.js 中的嵌套字典匹配的文档
- c# - DDD中的关系聚合根本身
- python - 如何在 Matplotlib 中绘制单个轮廓线
- java - 编译 vaadin 主题时显示错误
- windows - Ubuntu 中的 Windows FTP 清理脚本
- python - 如何为 Python cookiecutter-pylibrary 添加额外的库来打包内容?
- salesforce - 无法将沙盒的语言从葡萄牙语更改为英语
- html - Elements 的样式不显示文本装饰(下划线)?
- linux - sed 命令无法在 yaml 文件中查找和替换内容?
- ios - 物理 iOS 设备上 Flutter 应用中的网络请求很慢