求SQL语句?同行数据相减更新下行数据.(用循环)

Question

CREATE TABLE T
(
ITEM VARCHAR(20),
INUM VARCHAR(20),
LOT VARCHAR(20),
QTY INT,
TOTAL INT
)
INSERT T
SELECT 'A','1','1',12,45
UNION
SELECT 'A','1','2',11,45
UNION
SELECT 'A','1','3',3,45
UNION
SELECT 'B','1','1',20,69
UNION
SELECT 'B','1','2',7,69
UNION
SELECT 'B','1','3',13,69原始数据:
A        1        1        12        45
A        1        2        11        45
A        1        3        3        45
B        1        1        20        69
B        1        2        7        69
B        1        3        13        69改变的数据为,相同ITEM 的同个 INUM ,按lot顺序 由TOTAL-QTY 相减的数.
下一个批次LOT的QTY则由上个(TOTAL-QTY )-QTY .这样顺下来减.减后的数据为
A        1        1        23        45    (45-12=23)
A        1        2        12        45    (23-12=11)
A        1        3        9        45    (11-9=2)
B        1        1        49        69    (69-20=49)
B        1        2        42        69    (49-7=42)
B        1        3        29        69    (42-13=29)
这样的SQL怎么写.

Answer 1

CREATE TABLE T 
( 
ITEM VARCHAR(20), 
INUM VARCHAR(20), 
LOT VARCHAR(20), 
QTY INT, 
TOTAL INT 
) 
－－select a.*,tmp = (select(TOTAL -QTY ) from t where item = a.item and LOT <= a.lot )
from t a

Answer 2

CREATE TABLE T 
( 
ITEM VARCHAR(20), 
INUM VARCHAR(20), 
LOT VARCHAR(20), 
QTY INT, 
TOTAL INT 
) 
insert t select 
'a','1','1',12,45 union select
'A','1','2',11,45 union select
'A','1','3',3,45 union select 
'B','1','1',20,69 union select 
'B','1','2',7,69  union select
'B','1','3',13,69 
go
select a.*,tmp = (select sum(TOTAL - QTY ) from t where item = a.item and LOT <= a.lot )
from t adrop table t/*ITEM                 INUM                 LOT                  QTY         TOTAL       tmp         
-------------------- -------------------- -------------------- ----------- ----------- ----------- 
a                    1                    1                    12          45          33
A                    1                    2                    11          45          67
A                    1                    3                    3           45          109
B                    1                    1                    20          69          49
B                    1                    2                    7           69          111
B                    1                    3                    13          69          167（所影响的行数为 6 行）*/

Answer 3

--> 测试数据: @T
declare @T table (ITEM varchar(20),INUM varchar(20),LOT varchar(20),QTY int,TOTAL int)
insert into @T
select 'A',1,1,12,45 union all
select 'A',1,2,11,45 union all
select 'A',1,3,3,45 union all
select 'B',1,1,20,69 union all
select 'B',1,2,7,69 union all
select 'B',1,3,13,69select *, R = TOTAL - (select sum(QTY) from @T where ITEM=a.ITEM and LOT<=a.LOT) from @T a/*
ITEM                 INUM                 LOT                  QTY         TOTAL       R
-------------------- -------------------- -------------------- ----------- ----------- -----------
A                    1                    1                    12          45          33
A                    1                    2                    11          45          22
A                    1                    3                    3           45          19
B                    1                    1                    20          69          49
B                    1                    2                    7           69          42
B                    1                    3                    13          69          29
*/

Answer 4

哈哈，我写错了
CREATE TABLE T 
( 
ITEM VARCHAR(20), 
INUM VARCHAR(20), 
LOT VARCHAR(20), 
QTY INT, 
TOTAL INT 
) 
insert t select 
'a','1','1',12,45 union select
'A','1','2',11,45 union select
'A','1','3',3,45 union select 
'B','1','1',20,69 union select 
'B','1','2',7,69  union select
'B','1','3',13,69 
go
select a.*,tmp = TOTAL -(select sum(  QTY ) from t where item = a.item and LOT <= a.lot )
from t adrop table t/*ITEM                 INUM                 LOT                  QTY         TOTAL       tmp         
-------------------- -------------------- -------------------- ----------- ----------- ----------- 
a                    1                    1                    12          45          33
A                    1                    2                    11          45          22
A                    1                    3                    3           45          19
B                    1                    1                    20          69          49
B                    1                    2                    7           69          42
B                    1                    3                    13          69          29（所影响的行数为 6 行）*/

Answer 5

happyflystone  dawugui  你们的短消息已经收到了!