--你的表如果有主键就按下面的写法create table t(主键 int,品号 varchar(10), 未领用量 int , 库存数量 int) insert t select 1,'A',200 , 1000 union select 2,'A', 500 , 1000 union select 3,'A', 400 , 1000 go select *,库存结余=库存数量-(select sum(未领用量) from t where 品号=a.品号 and 主键<=a.主键) from t a go drop table t------------------- ---没有主键就要用临时表做 create table t(品号 varchar(10), 未领用量 int , 库存数量 int) insert t select 'A',200 , 1000 union select 'A', 500 , 1000 union select 'A', 400 , 1000 go select 主键=identity(int,1,1),* into #lsb from t select *,库存结余=库存数量-(select sum(未领用量) from #lsb where 品号=a.品号 and 主键<=a.主键) from #lsb a godrop table #lsb drop table t
--你的表如果有主键就按下面的写法create table t(主键 int,品号 varchar(10), 未领用量 int , 库存数量 int) insert t select 1,'A',200 , 1000 union select 2,'A', 500 , 1000 union select 3,'A', 400 , 1000 go select *,库存结余=库存数量-(select sum(未领用量) from t where 品号=a.品号 and 主键<=a.主键) from t a go drop table t------------------- ---没有主键就要用临时表做 create table t(品号 varchar(10), 未领用量 int , 库存数量 int) insert t select 'A',200 , 1000 union select 'A', 500 , 1000 union select 'A', 400 , 1000 go select 主键=identity(int,1,1),* into #lsb from t select *,库存结余=库存数量-(select sum(未领用量) from #lsb where 品号=a.品号 and 主键<=a.主键) from #lsb a godrop table #lsb drop table t
if object_id('t_naoxin') is not null drop table t_naoxin if object_id('f_he') is not null drop function f_he go create table t_naoxin(品号 varchar(10),未领用量 int,库存数量 int) insert t_naoxin select 'A', 200, 1000 union all select 'A', 500, 1000 union all select 'A', 400, 1000 go create function f_he(@id int) returns int as begin declare @t table(id int identity(0,1),品号 varchar(10),未领用量 int,库存数量 int) insert into @t select * from t_naoxin declare @i int set @i=0 select @i=sum(未领用量) from @t where id<=@id return @i end go declare @t table(id int identity(0,1),品号 varchar(10),未领用量 int,库存数量 int) insert into @t select * from t_naoxin select * from @t select 品号,未领用量,库存数量,库存数量-dbo.f_he(id) as 库存结余 from @t
insert t
select 1,'A',200 , 1000
union select 2,'A', 500 , 1000
union select 3,'A', 400 , 1000
go
select *,库存结余=库存数量-(select sum(未领用量) from t where 品号=a.品号 and 主键<=a.主键) from t a
go
drop table t-------------------
---没有主键就要用临时表做
create table t(品号 varchar(10), 未领用量 int , 库存数量 int)
insert t
select 'A',200 , 1000
union select 'A', 500 , 1000
union select 'A', 400 , 1000
go
select 主键=identity(int,1,1),* into #lsb from t
select *,库存结余=库存数量-(select sum(未领用量) from #lsb where 品号=a.品号 and 主键<=a.主键) from #lsb a
godrop table #lsb
drop table t
insert t
select 1,'A',200 , 1000
union select 2,'A', 500 , 1000
union select 3,'A', 400 , 1000
go
select *,库存结余=库存数量-(select sum(未领用量) from t where 品号=a.品号 and 主键<=a.主键) from t a
go
drop table t-------------------
---没有主键就要用临时表做
create table t(品号 varchar(10), 未领用量 int , 库存数量 int)
insert t
select 'A',200 , 1000
union select 'A', 500 , 1000
union select 'A', 400 , 1000
go
select 主键=identity(int,1,1),* into #lsb from t
select *,库存结余=库存数量-(select sum(未领用量) from #lsb where 品号=a.品号 and 主键<=a.主键) from #lsb a
godrop table #lsb
drop table t
drop table t_naoxin
if object_id('f_he') is not null
drop function f_he
go
create table t_naoxin(品号 varchar(10),未领用量 int,库存数量 int)
insert t_naoxin
select 'A', 200, 1000 union all
select 'A', 500, 1000 union all
select 'A', 400, 1000
go
create function f_he(@id int)
returns int
as
begin
declare @t table(id int identity(0,1),品号 varchar(10),未领用量 int,库存数量 int)
insert into @t select * from t_naoxin
declare @i int
set @i=0
select @i=sum(未领用量) from @t where id<=@id
return @i
end
go
declare @t table(id int identity(0,1),品号 varchar(10),未领用量 int,库存数量 int)
insert into @t select * from t_naoxin
select * from @t
select 品号,未领用量,库存数量,库存数量-dbo.f_he(id) as 库存结余 from @t