表A:
ID Value1 Value2
1 2
2 2
3 5
4 7更新输入值为6,按ID的顺序更新,希望的结果:
ID Value1 Value2
1 2 2
2 2 2
3 5 2
4 7
比较急,谢谢了
ID Value1 Value2
1 2
2 2
3 5
4 7更新输入值为6,按ID的顺序更新,希望的结果:
ID Value1 Value2
1 2 2
2 2 2
3 5 2
4 7
比较急,谢谢了
(ID Int,
Value1 Int,
Value2 Int)
Insert A Select 1, 2, Null
Union All Select 2, 2, Null
Union All Select 3, 5, Null
Union All Select 4, 7, Null
GO
Create Procedure SP_UpdateValue2(@Value2 Int)
As
Update
T1
Set
Value2 = (Case When T1.Value1 + T3.SUMValue1 < @Value2 Then T1.Value1
Else @Value2 - T3.SUMValue1 End)
From
A T1
Inner Join
(Select *, IsNull((Select SUM(Value1) From A Where ID < T2.ID), 0) As SUMValue1 From A T2) T3
On T1.ID = T3.ID
Where T3.SUMValue1 - @Value2 <= 0
GO
EXEC SP_UpdateValue2 6Select * From A
GO
Drop Table A
Drop Procedure SP_UpdateValue2
GO
--Result
/*
ID Value1 Value2
1 2 2
2 2 2
3 5 2
4 7 NULL
*/