关于更新表中的行而不使用循环

问题描述

嗨


我有一个有列数量的表，它对所有行都有一些值，例如。 200,150,200

和另一列quant = 500;



要求是：如果输入值是x = 500（或任何数字） ）那么这个值应该与数量列值进行比较，在下面的一个方面：



如果第一行的数量是200那么它应该从500减去它而x应该是更新为300，该行的数量应为0然后它应移至下一行，如果在第二行中数量列值为150则应将其与x ie.300的值进行比较，数量列值应为0和x = 150即。 （x =（x）300-数量列的值）...我们应该检查每一行的数量列值，直到x为0



你能帮我吗为此写一个SQL查询...



要求不要使用循环。



我在下面尝试过：



TestTav是包含数量和数量的表格quant

Hi
I am having table which has column quantity, it has some value for all rows eg. 200 , 150 , 200
and another column quant=500;

Requirement is: if a input value is x=500(or anynumber) then this value should be compared with quantity column value, in a fasion below:

if 1st row's quantity is 200 then it should subtract it form 500 and x should be updated to 300 and quantity of that row should be made 0 then It should move to next row, if in second row quantity column value is 150 then, it should be compared with values of x ie.300, quantity column value should be made to 0 and x=150 ie. (x=(x)300- value of quantity column)...we should check quantity column value in each row till x is 0

could you please help me write sql query for this...

it is ask that loops should not be used.

I tried below:

TestTav is table contains quantity & quant

With CTETest as
(
    Select TOP 1 Id,Name, CASE WHEN ((Quantity - Quant) < 0) THEN 0
    WHEN ((Quantity - Quant) >= 0) THEN (Quant - Quantity)
    Else
    Quantity
    END as Quantity,CASE WHEN (PrevQuant - Quant ) < 0 THEN (Quant - PrevQuant)
    Else
    0 End as Quant, PrevQuant, case WHEN ((PrevQuant - Quant) < 0) THEN (ID + 1) ELSE -1 END as myId from TestTav

    UNION ALL

    Select  TT.Id,TT.Name, CASE WHEN ((TT.Quantity - TT.Quant) < 0) THEN 0
    WHEN ((TT.Quantity - TT.Quant) >= 0) THEN (TT.Quant - TT.Quantity)
    Else
    TT.Quantity
    END as Quantity,CASE WHEN (TT.PrevQuant - TT.Quant ) < 0 THEN (TT.Quant - TT.PrevQuant)
    Else
    0 End as Quant, TT.PrevQuant, case WHEN ((TT.PrevQuant - TT.Quant) < 0) THEN (TT.ID + 1) ELSE -1 END as myId from TestTav TT
    inner join CTETest cte on cte.myId = TT.Id
)

Select * from CTETest

谢谢，

thanks,

解决方案

这篇关于关于更新表中的行而不使用循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！