有一个剩余数是1000(件),来源于下面的数据3000(件),5000(件),4000(件)用剩下的。
A 3000(件)
A 5000(件)
A 4000(件)
现在我要把上面数据(件)中用掉的数据算出来出来下面的结果
A 3000 3000
A 5000 5000
A 4000 3000这样的sql要怎么写呢。大家帮帮忙!
A 3000(件)
A 5000(件)
A 4000(件)
现在我要把上面数据(件)中用掉的数据算出来出来下面的结果
A 3000 3000
A 5000 5000
A 4000 3000这样的sql要怎么写呢。大家帮帮忙!
A 5000(件)
A 4000(件)为什么只是4000的减去1000 ?A 3000 3000
A 5000 5000
A 4000 3000
也就是说用掉了11000件还剩下1000件。数据如下:
A 3000(件)
A 5000(件)
A 4000(件)
现在我要把上面数据(件)中用掉的数据算出来出现下面的结果
A 3000 3000(用掉的)
A 5000 5000(用掉的)
A 4000 3000(用掉的)这样的sql要怎么写呢。大家帮帮忙!
insert into #(data) select 3000 union all select 5000 union all select 4000;with cte as
(
select *, sum(data)over(partition by type)-1000 used, (select sum(data) from # where type=t.type and id<=t.id)total from # t
)
select id, type, data, case when used>=total then data else data-total+used end what from cte/*
id type data what
----------- ---------- ----------- -----------
1 A 3000 3000
2 A 5000 5000
3 A 4000 3000
*/drop table #
A 3000 2000
A 5000 5000
A 4000 4000
不行吗?
--A 5000(件)
--A 4000(件)
--必须有一列表示先后顺序的列
declare @tmp table (id int identity(1,1),fname varchar(10),fnum int,fnum1 int default 0)
insert into @tmp (fname,fnum) select 'A',3000
insert into @tmp (fname,fnum) select 'A',5000
insert into @tmp (fname,fnum) select 'A',4000
insert into @tmp (fname,fnum) select 'B',3000
insert into @tmp (fname,fnum) select 'B',5000
insert into @tmp (fname,fnum) select 'B',4000
insert into @tmp (fname,fnum) select 'C',3000
insert into @tmp (fname,fnum) select 'C',5000
insert into @tmp (fname,fnum) select 'C',4000
--剩余数
declare @tmp2 table (fname varchar(10),fnum int)
insert into @tmp2 (fname,fnum)
select 'A',1000 --A剩余1000
union all select 'B',5000 --B剩余5000
union all select 'C',10000 --C剩余10000--先将fnum值赋给fnum1,再用一循环从最大ID行向上逐行比较与剩余数比较,如小于剩余数,减剩余数,清0,再与上一行比较
update @tmp set fnum1=fnum
while exists(select * from @tmp2 where fnum>0)
begin
update a set fnum1=case when a.fnum1>c.fnum then a.fnum1-c.fnum else 0 end
from @tmp a,(select max(id) as id,fname from @tmp where fnum=fnum1 group by fname) b,@tmp2 c
where a.fname=b.fname and a.id=b.id and a.fname=c.fname and c.fnum>0
update c set fnum=case when a.fnum>=c.fnum then 0 else c.fnum-a.fnum end
from @tmp a,(select min(id) as id,fname from @tmp where fnum<>fnum1 group by fname) b,@tmp2 c
where a.fname=b.fname and a.id=b.id and a.fname=c.fname and c.fnum>0
end
select * from @tmp
--A 5000(件)
--A 4000(件)
--必须有一列表示先后顺序的列
declare @tmp table (id int identity(1,1),fname varchar(10),fnum int,fnum1 int default 0)
insert into @tmp (fname,fnum) select 'A',3000
insert into @tmp (fname,fnum) select 'A',5000
insert into @tmp (fname,fnum) select 'A',4000
insert into @tmp (fname,fnum) select 'B',3000
insert into @tmp (fname,fnum) select 'B',5000
insert into @tmp (fname,fnum) select 'B',4000
insert into @tmp (fname,fnum) select 'C',3000
insert into @tmp (fname,fnum) select 'C',5000
insert into @tmp (fname,fnum) select 'C',4000
--剩余数
declare @tmp2 table (fname varchar(10),fnum int)
insert into @tmp2 (fname,fnum)
select 'A',1000 --A剩余1000
union all select 'B',5000 --B剩余5000
union all select 'C',10000 --C剩余10000--统计从最小向最大累加值,再与总数-剩余数比较,更新数据update a
set fnum1=case when b.fnum<d.fnum-c.fnum then a.fnum --累计数<总消费数
when b.fnum-(d.fnum-c.fnum)<=a.fnum then a.fnum-b.fnum+d.fnum-c.fnum else 0 end
from @tmp a,
(select a.id,a.fname,sum(b.fnum) fnum from @tmp a,@tmp b
where a.fname=b.fname and a.id>=b.id group by a.id,a.fname) b,
@tmp2 c,
(select fname,sum(fnum) fnum from @tmp group by fname) d
where a.id=b.id and a.fname=b.fname and a.fname=c.fname and c.fname=d.fnameselect * from @tmp
结果
id fname fnum fnum1
----------- ---------- ----------- -----------
1 A 3000 3000
2 A 5000 5000
3 A 4000 3000
4 B 3000 3000
5 B 5000 4000
6 B 4000 0
7 C 3000 2000
8 C 5000 0
9 C 4000 0