精典sql语句求结存数的问题
有以下一个数据表:表名(扣数表)
物料编号 时间 入库数量 出库数量 结存数
0001 2007-1-29 4000 3000
0001 2007-1-30 300 60
0001 2007-1-31 0 80 80
以上数据表中0001物料编号的初始库存数为0，要求用sql语句自动计数结存数
计算后的结存数年的结果为(以时间为关键字,根据上一条的结果计算本条的结果)：
物料编号 时间 入库数量 出库数量 结存数
0001 2007-1-29 4000 3000 1000
0001 2007-1-30 300 60 1240
0001 2007-1-31 0 80 1180
希望有高手能帮忙解决问题,在此感激不尽
[解决办法]
create table 扣数表
(
物料编号 varchar(4),
时间 datetime,
入库数量 int,
出库数量 int,
结存数 int
)
insert into 扣数表 select '0001 ', '2007-1-29 ', 4000, 3000,0
insert into 扣数表 select '0001 ', '2007-1-30 ', 300, 60, 0
insert into 扣数表 select '0001 ', '2007-1-31 ', 0, 80, 0
--更新
update t1 set 结存数 = t2.结存数
from 扣数表 t1,
(select 物料编号,时间,
结存数 = (select sum(入库数量 - 出库数量) from 扣数表 where 物料编号 = a.物料编号 and 时间 <= a.时间)
from 扣数表 a
)t2
where t1.物料编号 = t2.物料编号 and t1.时间 = t2.时间
--查询
select * from 扣数表
--结果
00012007-01-29 00:00:00.000400030001000
00012007-01-30 00:00:00.000300601240
00012007-01-31 00:00:00.0000801160
[解决办法]
declare @表名 table
(物料编号 int, 时间 datetime, 入库数量 int, 出库数量 int, 结存数 int
)
insert @表名
select 0001, '2007-1-29 ', 4000, 3000, ' '
union all
select 0001, '2007-1-30 ', 300, 60, ' '
union all
select 0001, '2007-1-31 ', 0, 80, ' '
select 物料编号,sum(结存数+入库数量-出库数量)
from @表名
group by 物料编号
（所影响的行数为 3 行）
物料编号
----------- -----------
1 1160
（所影响的行数为 1 行）
[解决办法]
--借用楼上数据:
--环境
create table 扣数表
(
物料编号 varchar(4),
时间 datetime,
入库数量 int,
出库数量 int,
结存数 int
)
insert into 扣数表 select '0001 ', '2007-1-29 ', 4000, 3000,0
insert into 扣数表 select '0001 ', '2007-1-30 ', 300, 60, 0
insert into 扣数表 select '0001 ', '2007-1-31 ', 0, 80, 0
-查询
select 物料编号,时间,入库数量,出库数量,
结存数=(select sum((入库数量-出库数量)) from 扣数表 where 物料编号=ta.物料编号 and 时间 <=ta.时间)
from 扣数表 ta
--结果
物料编号 时间 入库数量 出库数量 结存数
---- ------------------------------------------------------ ----------- ----------- -----------
0001 2007-01-29 00:00:00.000 4000 3000 1000
0001 2007-01-30 00:00:00.000 300 60 1240
0001 2007-01-31 00:00:00.000 0 80 1160
（所影响的行数为 3 行）
--删除测试环境
drop table 扣数表
[解决办法]
create table 扣数表(
物料编号 varchar(4),
时间 datetime,
入库数量 int,
出库数量 int,
结存数 int)
insert into 扣数表 select '0001 ', '2007-1-29 ',4000,3000,0
insert into 扣数表 select '0001 ', '2007-1-30 ',300, 60, 0
insert into 扣数表 select '0001 ', '2007-1-31 ',0, 80, 0
update 扣数表
set 结存数=(select isnull(sum(入库数量 - 出库数量),0)
from 扣数表 tt
where TT.时间 <=扣数表.时间)
select * from 扣数表
drop table 扣数表
物料编号 时间 入库数量 出库数量 结存数
-------- -------------------------- ----------- ----------- ------
0001 2007-01-29 00:00:00.000 4000 3000 1000
0001 2007-01-30 00:00:00.000 300 60 1240
0001 2007-01-31 00:00:00.000 0 80 1160
[解决办法]
create table 扣数表(物料编号 varchar(10), 时间 datetime, 入库数量 int, 出库数量 int, 结存数 int)
insert 扣数表 select '0001 ' , '2007-1-29 ', 4000, 3000, null
union all select '0001 ', '2007-1-30 ', 300, 60, null
union all select '0001 ', '2007-1-31 ', 0, 80, 80
select 物料编号, 时间, 入库数量, 出库数量,
结存数=(select sum(入库数量)-sum(出库数量) from 扣数表 where 物料编号=tmp.物料编号 and 时间 <=tmp.时间)
from 扣数表 as tmp
--result
物料编号 时间 入库数量 出库数量 结存数
---------- ------------------------------------------------------ ----------- ----------- -----------
0001 2007-01-29 00:00:00.000 4000 3000 1000
0001 2007-01-30 00:00:00.000 300 60 1240
0001 2007-01-31 00:00:00.000 0 80 1160
(3 row(s) affected)
[解决办法]
if exists (select name from sysobjects where name= 'aa ')
drop table aa
create table aa
(
物料编号 varchar(10), 时间 datetime, 入库数量 float , 出库数量 float, 结存数 float)
insert into aa
select '0001 ', '2007-1-29 ', 4000, 3000 ,null union all
select '0001 ', '2007-1-30 ', 300, 60,null union all
select '0001 ', '2007-1-31 ', 0, 80,null union all
select '0002 ', '2007-1-31 ', 3500, 500,null union all
select '0002 ', '2007-2-05 ', 0, 1000,null
以上数据表中0001物料编号的初始库存数为0，要求用sql语句自动计数结存数
计算后的结存数年的结果为(以时间为关键字,根据上一条的结果计算本条的结果)：
物料编号 时间 入库数量 出库数量 结存数
0001 2007-1-29 4000 3000 1000
0001 2007-1-30 300 60 1240
0001 2007-1-31 0 80 1180
select * from aa
declare ee cursor for select 物料编号,时间,入库数量,出库数量 from aa order by 物料编号,时间
declare @el_no varchar(10),@el_no1 varchar(10),@datetime datetime,@inqty float,@outqty float,@kk float
open ee
fetch next from ee into @el_no,@datetime,@inqty,@outqty
set @el_no1=@el_no
set @kk=0
while (@@fetch_status=0)
begin
if @el_no1=@el_no
begin
set @kk=@kk+@inqty-@outqty
update aa set 结存数=@kk where 物料编号 =@el_no and 时间=@datetime
end
else
begin
set @kk=0
set @el_no1=@el_no
set @kk=@kk+@inqty-@outqty
update aa set 结存数=@kk where 物料编号 =@el_no and 时间=@datetime
end
fetch next from ee into @el_no,@datetime,@inqty,@outqty
end
select * from aa
close ee
deallocate ee
所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
物料编号 时间 入库数量 出库数量 结存数
---------- ------------------------------------------------------ ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
0001 2007-01-29 00:00:00.000 4000.0 3000.0 1000.0
0001 2007-01-30 00:00:00.000 300.0 60.0 1240.0
0001 2007-01-31 00:00:00.000 0.0 80.0 1160.0
0002 2007-01-31 00:00:00.000 3500.0 500.0 3000.0
0002 2007-02-05 00:00:00.000 0.0 1000.0 2000.0
（所影响的行数为 5 行）