问题描述
我有一个问题,我不知道如何解决..这是代码和想要的结果
I have a problem that I don't know how to fix .. here is the code and wanting result
if object_id('tempdb..#A') IS NOT NULL drop table #A create table #A (ID int, Value decimal(6,2), value2 decimal(6,2), Result decimal(6,2)) insert into #A (ID, Value, value2, Result) values (1, 10, 25, null), (1, 10, 25, null), (1, 10, 25, null), (2, 10, 5, null), (2, 10, 5, null), select * from #A
所以,我想从value2"中取出价值,如果有剩余,只需将其更新为 0,对于 下一行,我将采取那些剩余"并用它们带走,与下一个价值
So, I would like to take Value away from "value2", if there are left overs, just update it to 0, for next row i would take those "left overs" and use them to take away from, with next Value
我想得到这样的结果...
I would like to get results like this...
ID Value value2 Result 1 10 25 0 ---------------------------- 1 10 25 0 ---------------------------- 1 10 25 5 ---------------------------- 2 10 5 5 ---------------------------- 2 10 5 10
如您所见,ID 为 1 ... 应该是:
So as you can see with ID 1 ... it would be:
10 - 25 = 0 10 - 15 = 0 10 - 5 = 5
我希望你明白我在这里想要做什么......如果我能解释更多,请告诉我......
I hope you understand what I am trying to do here ... let me know if I can explain more ...
推荐答案
在 Gordon 的帮助下并使用了他的部分想法......我做了一些事情,目前看来可行,但还需要更多测试
With help of Gordon and using some part of his idea ... i did something, that at this moment seems to work, but will need a lot of more testing
if object_id('tempdb..#testDataWithRunningTotal') IS NOT NULL drop table #testDataWithRunningTotal select id, value, value2, cast(null as float) as Result into #testDataWithRunningTotal from #A order by id; declare @runningTotal float = 0, @previousParentId int = null; update #testDataWithRunningTotal set @runningTotal = Result = case when @previousParentId <> id then value2 - value else case when ISNULL(@runningTotal,0) < 0 then value * (-1) when value2 - value < 0 and ISNULL(@runningTotal,0) = 0 then value2 when value2 - value > 0 and ISNULL(@runningTotal,0) = 0 then value2 - value else case when @runningTotal - value < 0 and ISNULL(@runningTotal,0) = 0 then value else @runningTotal - value end end end, @previousParentId = id from #testDataWithRunningTotal update tst set Result = case when Result > 0 then 0 else Result * -1 end from #testDataWithRunningTotal tst select * from #testDataWithRunningTotal
所以,我保持@runningTotal 运行更新,并允许它低于 0 ......一旦它小于 0,这意味着值的总和大于 Value2 的总和的时刻......所以我将记录保留在那里,并在此计算结束时进行更新.
So, I am keeping @runningTotal running with update, and allowing it to go under 0 ... once it goes less then 0 it means that is moment where SUM of value is greater then SUM of Value2 ... so i keep the record there, and at end of this calculation i do update.