表A:ID,名称,采购数量
01,AA,20
02,AA,30
03,AA,30
04,AA,40
05,AA,50
06,AA,10
07,AA,20求,如果AA的和=100是,按照ID顺序领用的ID、采购数量,领用数量
结果:
01,AA,20,20
02,AA,30,30
03,AA,30,30
04,AA,40,20如何写出来的查询简单?
01,AA,20
02,AA,30
03,AA,30
04,AA,40
05,AA,50
06,AA,10
07,AA,20求,如果AA的和=100是,按照ID顺序领用的ID、采购数量,领用数量
结果:
01,AA,20,20
02,AA,30,30
03,AA,30,30
04,AA,40,20如何写出来的查询简单?
不知道怎么写。
表A:ID,名称,采购数量,累计库存
01,AA,20 ,20
02,AA,30 ,50
03,AA,30 ,80
04,AA,40 ,120
05,AA,50 ,170
06,AA,10 ,180
07,AA,20 ,200
select ID,名称,采购数量,采购数量 as 领用数量 from 进销存 where 累计库存<100
union
select ID,名称,采购数量,采购数量 - (select max(累计库存) as 累计领用 from 进销存 where 累计库存<100 ) as 领用数量
from 进销存 where ID in (select min(id) from 进销存 where 累计库存>=100 )
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] varchar(2),[名称] varchar(2),[采购数量] int)
insert [A]
select '01','AA',20 union all
select '02','AA',30 union all
select '03','AA',30 union all
select '04','AA',40 union all
select '05','AA',50 union all
select '06','AA',10 union all
select '07','AA',20
---查询---
select * from
(
select
ID,
名称,
采购数量-(case when 库存<0 then 0 else 库存 end) as 采购数量
from
(select *,(select sum(采购数量) from A where id<=t.id)-100 as 库存 from [A] t) t
) t
where 采购数量>0
---结果---
ID 名称 采购数量
---- ---- -----------
01 AA 20
02 AA 30
03 AA 30
04 AA 20(所影响的行数为 4 行)