一个复杂的存储过程,得到答案立刻接贴(在线等)

Question

我把测试数据给罗列出来了
CREATE TABLE test(
[id] int identity(1,1) not null primary key,
go_id int not null,
qty int not null,
price decimal(8,4)
)
goINSERT  test SELECT 1,10,2.5
UNION ALL SELECT 1,5,3.0
UNION ALL SELECT 1,2,2.0
UNION ALL SELECT 1,8,4.0
UNION ALL SELECT 1,3,8.0
UNION ALL SELECT 2,2,3.0
UNION ALL SELECT 2,3,2.0
UNION ALL SELECT 2,4,4.0我需要一个这样的存储过程,输入2个参数(@go_id,@sum_qty),go_id和test表里的go_id对应
@sum_qty为输入的一个整数,首先要确保sum_qty的值<sum(qty) where go_id = @go_id
当满足条件后,根据先进先出法,修改qty的值,修改的顺序是根据ID来修改的
我做一个测试,比如存储过程名为sales
调用exec sales(1,20)
然后select * from test的值为
id      go_id     qty       price
1 1 0 2.5000
2 1 0 3.0000
3 1 0 2.0000
4 1 5 4.0000
5 1 3 8.0000
6 2 2 3.0000
7 2 3 2.0000
8 2 4 4.0000

Answer 1

--建立測試環境
CREATE TABLE test(
[id] int identity(1,1) not null primary key,
go_id int not null,
qty int not null,
price decimal(8,4)
)
go
--插入數據
INSERT  test SELECT 1,10,2.5
UNION ALL SELECT 1,5,3.0
UNION ALL SELECT 1,2,2.0
UNION ALL SELECT 1,8,4.0
UNION ALL SELECT 1,3,8.0
UNION ALL SELECT 2,2,3.0
UNION ALL SELECT 2,3,2.0
UNION ALL SELECT 2,4,4.0
GO
--建立存儲過程
Create ProceDure sales(@go_id Int,@saleqty Int)
As
Begin
Update A Set qty = (Case When (Select SUM(qty) From test Where go_id = A.go_id And id <= A.id ) <= @saleqty 
Then 0 
When (Select SUM(qty) From test Where go_id = A.go_id And id < A.id ) > @saleqty 
Then qty
Else (Select SUM(qty) From test Where go_id = A.go_id And id <= A.id ) - @saleqty
End)
From test A Where go_id = @go_id
End
GO
--測試
exec sales 1,20
--exec sales 1,8
--exec sales 1,13
Select * From test
GO
--刪除測試環境
Drop Table test
Drop ProceDure sales
GO
--結果
/*
[id] go_id qty price
1 1 0 2.5000
2 1 0 3.0000
3 1 0 2.0000
4 1 5 4.0000
5 1 8 8.0000
6 2 2 3.0000
7 2 3 2.0000
8 2 4 4.0000
*/

Answer 2

CREATE TABLE test
(
[id]    int identity(1,1) not null primary key,
go_id   int not null,
qty int not null,
price decimal(8,4)
)
goINSERT  test SELECT 1,10,2.5
UNION ALL SELECT 1,5,3.0
UNION ALL SELECT 1,2,2.0
UNION ALL SELECT 1,8,4.0
UNION ALL SELECT 1,3,8.0
UNION ALL SELECT 2,2,3.0
UNION ALL SELECT 2,3,2.0
UNION ALL SELECT 2,4,4.0
--SELECT * FROM TEST
GOCREATE PROC SALES
  @ID  INT,
  @QTY INT
AS
BEGIN
  DECLARE @I INT  WHILE @QTY>0
  BEGIN
    UPDATE TEST SET 
    @I =(CASE WHEN QTY>@QTY THEN @QTY ELSE QTY END),
    QTY=(CASE WHEN QTY>@QTY THEN QTY-@QTY ELSE 0 END) 
    WHERE ID =(SELECT MIN(ID) FROM TEST WHERE GO_ID=@ID AND QTY>0)    SET @QTY=@QTY-@I
  END
END
GOEXEC SALES 1,20
SELECT * FROM TESTDROP PROC SALES
DROP TABLE TEST