豆子,你好,
--来了,不得不说你这样的表结构很不合理
create function dbo.f_str(@id int) returns varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+' ','')+入库编号+'-'+ltrim(sl) from
(select * ,sl =case when (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<=tb.id and id<=@id)
<=(select isnull(abs(sum(数量)),0) from tb where 数量<0 and id<=@id) then 0
when (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<tb.id and id<=@id)
>=(select isnull(abs(sum(数量)),0) from tb where 数量<0 and id<=@id) then 数量
else (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<=tb.id and id<=@id)
-(select isnull(abs(sum(数量)),0) from tb where 数量<0 and id<=@id)
end
from tb
where 入库编号 is not null and id<=@id)tmp
where 入库编号 is not null and sl>0
return(@sql)
end
go
select *,(select sum(数量) from tb a where a.id<=tb.id)库存,dbo.f_str(id)当前使用编号 from tbdrop function dbo.f_str
/*
id 入库编号 数量 库存 当前使用编号
----------- ---------- ----------- ----------- ----------------------
1 L001 5 5 L001-5
2 L002 3 8 L001-5 L002-3
3 NULL -3 5 L001-2 L002-3
4 NULL -4 1 L002-1
5 L005 6 7 L002-1 L005-6
*/
--来了,不得不说你这样的表结构很不合理
create function dbo.f_str(@id int) returns varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+' ','')+入库编号+'-'+ltrim(sl) from
(select * ,sl =case when (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<=tb.id and id<=@id)
<=(select isnull(abs(sum(数量)),0) from tb where 数量<0 and id<=@id) then 0
when (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<tb.id and id<=@id)
>=(select isnull(abs(sum(数量)),0) from tb where 数量<0 and id<=@id) then 数量
else (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<=tb.id and id<=@id)
-(select isnull(abs(sum(数量)),0) from tb where 数量<0 and id<=@id)
end
from tb
where 入库编号 is not null and id<=@id)tmp
where 入库编号 is not null and sl>0
return(@sql)
end
go
select *,(select sum(数量) from tb a where a.id<=tb.id)库存,dbo.f_str(id)当前使用编号 from tbdrop function dbo.f_str
/*
id 入库编号 数量 库存 当前使用编号
----------- ---------- ----------- ----------- ----------------------
1 L001 5 5 L001-5
2 L002 3 8 L001-5 L002-3
3 NULL -3 5 L001-2 L002-3
4 NULL -4 1 L002-1
5 L005 6 7 L002-1 L005-6
*/
把下面这句
(select * ,sl =case when (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<=tb.id and id<=@id)
改为下面这样或许略好点
(select 入库编号,sl =case when (select isnull(sum(数量),0) from tb a where 数量>0 and a.id<=tb.id and id<=@id)
这样查询的时候直接select 当前使用编号就可以了,
一段时间后再去 update 仅仅当前使用编号为空的记录就可以了
这样就不存在效率问题了
update tb
set 当前使用编号= dbo.f_str(tb.id)
where isnull(当前使用编号,'')=''