34,593
社区成员
发帖
与我相关
我的任务
分享
create table t1
(
id int,
kucun int
)
insert into t1
select 1, 2 union all
select 2, 3 union all
select 3, 5
select * from t1
select a.*,case when (select SUM(kucun) as kucun from t1 where id<=a.id)<=8 then kucun else 8-(select SUM(kucun) as kucun from t1 where id<a.id) end as '出库' from t1 as a
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int,
[count] int
)
go
insert into tb
select 1,2 union all
select 2,3 union all
select 3,5
go
update tb set [count]=case when (select sum([count]) from tb where id<=a.id)<8 then 0 else (select sum([count]) from tb where id<=a.id)-8 end from tb a
select * from tb
/*
id count
----------- -----------
1 0
2 0
3 2
(3 行受影响)
*/
Create table #T([ID] int,[Count] int)
Insert #T
select 1,2 union all
select 2,3 union all
select 3,5
select a.ID,a.Count, case when a.Subtotal >=0 then a.Subtotal else a.Count end Subtotal from (
select *,(select sum([Count])-8
from #T where [Count]<=t.[Count]) as Subtotal
from #T t) a
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Count] int)
Insert #T
select 1,2 union all
select 2,3 union all
select 3,5
Go
select
ID,[Count],DeliveryQty=Case when [Total]<=8 then [Count] else 8+[Count]-[Total] end
from (Select * ,(select SUM([Count]) from #T where ID<=a.ID) as [Total]
from #T as a
)t
where [Total]-[Count]<8
/*
ID Count DeliveryQty
1 2 2
2 3 3
3 5 3
*/