用游标来求现存量

Question

表如下：
名称 日期 入库 出库 现存
AA 2012-4-1 22 0
AA 2012-4-2 22 0
AA 2012-4-3 22 0
BB 2012-4-4 44 0
BB 2012-4-5 22 0
BB 2012-4-6 22 0怎样得到现存量呢？

Answer 1

CREATE TABLE tbd
(
name VARCHAR(10),
TDATE DATETIME,
inV INT,
outV INT,
Surplus INT
)
INSERT INTO tbd
SELECT 'AA', '2012-4-1', 22, 0, 0 UNION
SELECT 'AA', '2012-4-2', 22, 0,0 UNION
SELECT 'AA', '2012-4-3', 22, 0,0 UNION
SELECT 'BB', '2012-4-4', 44, 0,0 UNION
SELECT 'BB', '2012-4-5', 22, 0,0 UNION
SELECT 'BB', '2012-4-6', 22, 0,0SELECT name,Tdate,inV,outV,(select SUM(inV) - SUM(outV) FROM tbd AS B where B.Tdate <= A.Tdate) AS Surplus
FROM tbd AS A
name Tdate inV outV Surplus
AA 2012-04-01 00:00:00.000 22 0 22
AA 2012-04-02 00:00:00.000 22 0 44
AA 2012-04-03 00:00:00.000 22 0 66
BB 2012-04-04 00:00:00.000 44 0 110
BB 2012-04-05 00:00:00.000 22 0 132
BB 2012-04-06 00:00:00.000 22 0 154
直接在原表中更新UPDATE tbd SET tbd.Surplus = tablea.Surplus FROM (SELECT name,Tdate,inV,outV,(select SUM(inV) - SUM(outV) FROM tbd AS B where B.Tdate <= A.Tdate) AS Surplus
FROM tbd AS A) AS tablea WHERE tbd.name = tablea.name AND tbd.TDATE = tablea.TDATE

Answer 2

--insert into tb22
--Select 'aa',20,0,0 union
--Select 'aa',60,10,0 union
--Select 'bb',15,10,0 union
--Select 'bb',40,0,0 
declare @现存 float
declare @名称 varchar(10)
declare @入库 float
declare @出库 float
Set @现存=0
Set @名称='aa'
declare cur cursor local for Select 入库,出库 From tb22 Where 名称=@名称
open cur 
Fetch cur into @入库,@出库
While @@Fetch_Status=0
begin
Set @现存=@入库-@出库
Fetch cur into @入库,@出库

end
print(@现存)
close cur
deallocate cur--返回结果50

Answer 3

--> 测试数据：[test]
go
if object_id('[test]') is not null 
drop table [test]
go
create table [test](
[名称] varchar(2),
[日期] date,
[入库] int,
[出库] int,
[现存] int
)
go
insert [test]
select 'AA','2012-4-1',22,0,null union all
select 'AA','2012-4-2',22,0,null union all
select 'AA','2012-4-3',22,0,null union all
select 'BB','2012-4-4',44,0,null union all
select 'BB','2012-4-5',22,0,null union all
select 'BB','2012-4-6',22,0,null;with t
as(
    select px=ROW_NUMBER()over(partition by [名称] order by getdate()),
        * from test
),
m as(
select px,名称,日期,入库,出库,现存=入库-出库 from  t where px=1
union all
select a.px,a.名称,a.日期,a.入库,a.出库,b.现存+a.入库-a.出库 from t a
inner join m b on a.px=b.px+1 and a.名称=b.名称
)
select 名称,日期,入库,出库,现存 from m order by [名称]
/*
名称 日期 入库 出库 现存
AA 2012-04-01 22 0 22
AA 2012-04-02 22 0 44
AA 2012-04-03 22 0 66
BB 2012-04-04 44 0 44
BB 2012-04-05 22 0 66
BB 2012-04-06 22 0 88
*/--递归一下

Answer 4

drop table #tb1
SELECT 'AA' name, '2012-4-1' date, 22 inStock, 0 outStock, 0 nowStock into #tb1 
UNION SELECT 'AA', '2012-4-2', 22, 9,0 UNION
SELECT 'AA', '2012-4-3', 22, 2,0 UNION
SELECT 'DD', '2012-4-4', 44, 6,0 UNION
SELECT 'BB', '2012-4-5', 22, 3,0 UNION
SELECT 'BB', '2012-4-6', 22, 0,0 
order by name,datedeclare @nowStock int=0
declare @name nvarchar(20)=''
update #tb1 set @name=case when @name<>name then name end,
@nowStock=@nowStock+inStock-outStock,nowStock=@nowStock 
from #tb1

Answer 5

测试下来 4，7 楼结果不对啊
现在又想增加一个字段 出库日期 如
名称 日期 出库日期 入库 出库 现存
AA 2012-4-1 22 0 0
AA 2012-4-2 22 0 0
AA 2012-4-3 0 22 0
BB 2012-4-4 44 0 0
BB 2012-4-5 0 22 0
BB 2012-4-6 0 22 0如是按每个产品的先进先出法来计算出库日期 怎样来更新出库日期呢
越来越难了

Answer 6

算法不对吧 我这里需要按AA BB  来统计现存量的 出来的现存量是累计了
结果如下
名称      日期 出库日期 入库 出库 现存
AA 2012-4-1 2012-4-3 22 0 22
AA 2012-4-2 22 0 44
AA 2012-4-3 0 22 22
BB 2012-4-4 2012-4-6 44 0 44
BB 2012-4-5 0 22 22
BB 2012-4-6 0 22 0

Answer 7

drop table #tb1
SELECT 'AA' name, '2012-4-1' date, 22 inStock, 0 outStock, 0 nowStock into #tb1  
UNION SELECT 'AA', '2012-4-2', 22, 0,0 UNION
SELECT 'AA', '2012-4-3', 0, 22,0 UNION
SELECT 'BB', '2012-4-4', 44, 0,0 UNION
SELECT 'BB', '2012-4-5', 0, 22,0 UNION
SELECT 'BB', '2012-4-6', 0, 22,0 
order by name,datedeclare @nowStock int=0
declare @name nvarchar(20)=''
update #tb1 set 
@nowStock=case when @name<>name then inStock-outStock else @nowStock+inStock-outStock end,
@name=case when @name<>name then name else @name end,nowStock=@nowStock
from #tb1select * from #tb1

Answer 8

DROP TABLE tbd
CREATE TABLE tbd
(
    name VARCHAR(10),
    TDATE DATETIME,
    inV INT,
    outV INT,
    Surplus INT
)
INSERT INTO tbd
SELECT 'AA', '2012-4-1', 22, 0, 0 UNION
SELECT 'AA', '2012-4-2', 22, 0,0 UNION
SELECT 'AA', '2012-4-3', 0, 22,0 UNION
SELECT 'BB', '2012-4-4', 44, 0,0 UNION
SELECT 'BB', '2012-4-5', 0, 22,0 UNION
SELECT 'BB', '2012-4-6', 0, 22,0SELECT name,Tdate,inV,outV,(select SUM(inV) - SUM(outV) FROM tbd AS B where B.Tdate <= A.Tdate AND A.name = B.name) AS Surplus
FROM tbd AS Aname Tdate inV outV Surplus
AA 2012-04-01 00:00:00.000 22 0 22
AA 2012-04-02 00:00:00.000 22 0 44
AA 2012-04-03 00:00:00.000 0 22 22
BB 2012-04-04 00:00:00.000 44 0 44
BB 2012-04-05 00:00:00.000 0 22 22
BB 2012-04-06 00:00:00.000 0 22 0
至于你说的加上出库日期，规则好像没说明白。先入先出的话，比如2012-04-01入库22,2012-04-02出库11，0,22-04-03出库11，这时候按你给出的结果2012-04-01对应的出库日期是多少呢？02还是03？
还有，2012-04-01入库22，2012-04-02入库22，2012-04-03出库11，2012-04-04出库22的话，2012-04-01对应的出库日期是2012-04-03还是2012-04-04？这两天都要从2012-04-01里面出11，2012-04-04还要从2012-04-02里出11怎么办？

Answer 9

--> 测试数据：[test]
go
if object_id('[test]') is not null 
drop table [test]
go
create table [test](
[名称] varchar(2),
[日期] date,
[入库] int,
[出库] int,
[现存] int
)
go
insert [test]
select 'AA','2012-4-1',22,0,null union all
select 'AA','2012-4-2',22,0,null union all
select 'AA','2012-4-3',22,0,null union all
select 'BB','2012-4-4',44,0,null union all
select 'BB','2012-4-5',22,0,null union all
select 'BB','2012-4-6',22,0,null
go
alter table [test] add px int
go
update  [test]
set px=(select COUNT(*) from test a where a.名称=b.名称 and a.日期<=b.日期) from test b;with t
as(
    select row=ROW_NUMBER()over(partition by [名称] order by getdate()),
        * from test
),
m as(
select row,名称,日期,入库,出库,现存=入库-出库 from  t where row=1
union all
select a.row,a.名称,a.日期,a.入库,a.出库,b.现存+a.入库-a.出库 from t a
inner join m b on a.row=b.row+1 and a.名称=b.名称
)
update test set [现存]=m.现存 from m where test.名称=m.名称 and test.px=m.rowalter table test drop column px
go
select * from test
/*
名称 日期 入库 出库 现存
AA 2012-04-01 22 0 22
AA 2012-04-02 22 0 44
AA 2012-04-03 22 0 66
BB 2012-04-04 44 0 44
BB 2012-04-05 22 0 66
BB 2012-04-06 22 0 88
*/