SQL 循环操作
发布网友
发布时间：2022-04-26 05:06
共2个回答
热心网友
时间：2022-06-21 00:02
Create Table #Tmp
(
ID int identity(1,1),
货号 Varchar(10),
订货数量 int,
库存 int,
发货数量 int
)
Insert Into #Tmp(货号,订货数量,库存,发货数量) Values('SP001',10,28,0)
Insert Into #Tmp(货号,订货数量,库存,发货数量) Values('SP001',30,28,0)
Insert Into #Tmp(货号,订货数量,库存,发货数量) Values('SP002',30,10,0)
Insert Into #Tmp(货号,订货数量,库存,发货数量) Values('SP002',6,10,0)
Insert Into #Tmp(货号,订货数量,库存,发货数量) Values('SP002',5,10,0)
Insert Into #Tmp(货号,订货数量,库存,发货数量) Values('SP003',3,28,0)
Declare @ID Int
DECLARE @货号 Varchar(10)
Declare @旧货号 Varchar(10)
Declare @订货数量 int
Declare @库存 int
Declare @Tmp int
Set @旧货号=''
DECLARE vendor_cursor CURSOR FOR
Select ID,货号,订货数量,库存 From #Tmp Order By 货号
OPEN vendor_cursor;
FETCH NEXT FROM vendor_cursor
INTO @ID,@货号, @订货数量,@库存
WHILE @@FETCH_STATUS = 0
BEGIN
if (@旧货号<>@货号)
Begin
Set @旧货号=@货号
Set @Tmp=@库存
End
--库存小于0,不发货
if @Tmp<=0
Update #Tmp Set 库存=@Tmp Where ID=@ID
Else
Begin
--订货数小于等于库存数,发订货数,否则发库存数
if (@订货数量<=@Tmp)
Begin
Update #Tmp Set 发货数量=@订货数量,库存=@Tmp Where ID=@ID
Set @Tmp=@Tmp-@订货数量
End
Else
Begin
Update #Tmp Set 发货数量=@Tmp,库存=@Tmp Where ID=@ID
Set @Tmp=@Tmp-@Tmp
End
End
FETCH NEXT FROM vendor_cursor
INTO @ID,@货号, @订货数量,@库存
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
Select * From #Tmp
Drop Table #Tmp
热心网友
时间：2022-06-21 00:02
