問題描述

我有一個問題，我不知道如何解決..這是代碼和想要的結果

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.

這篇關于SQL 從運行總數中刪除的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！