SQL 从运行总数中删除

2021-09-10 00:00:00 sql tsql sql-server

我有一个问题，我不知道如何解决..这是代码和想要的结果

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.

相关文章