现有表 tblTemp
其记录如下:
noid fQty(数量) 修改后的 noid fQty(数量)
1 100 1 100
2 -40 2 0
3 10 3 0
4 50 4 20
5 -20 5 0
6 30 6 10
现在希望对表tblTEmp进行修改(记录中fQty<0的记录,与其下面最近的行fQty>0的合并,如果负数的绝对值大于下面行的数量,则将下面的行的数量清空,其差值再与再下面一行的记录进行合并,直到将负数全部修改为0)
其记录如下:
noid fQty(数量) 修改后的 noid fQty(数量)
1 100 1 100
2 -40 2 0
3 10 3 0
4 50 4 20
5 -20 5 0
6 30 6 10
现在希望对表tblTEmp进行修改(记录中fQty<0的记录,与其下面最近的行fQty>0的合并,如果负数的绝对值大于下面行的数量,则将下面的行的数量清空,其差值再与再下面一行的记录进行合并,直到将负数全部修改为0)
其记录如下:
noid fQty(数量)
1 100
2 -40
3 10
4 50
5 -20
6 30
修改后的
noid fQty(数量)
1 100
2 0
3 0
4 20
5 0
6 10现在希望对表tblTEmp进行修改(记录中fQty<0的记录,与其下面最近的记录fQty>0的汇总,如果汇总后的数量还是负数,则用该负数与其下面的记录再进行汇总,直到将负数与正数全部汇总)
insert into tblTemp select 1, 100
insert into tblTemp select 2, -40
insert into tblTemp select 3, 10
insert into tblTemp select 4, 50
insert into tblTemp select 5, -20
insert into tblTemp select 6, 30
go
;with cte as(
select *,(case when fqty>=0 then 0 else fqty end)as f from tblTemp where noid=1
union all
select a.noid,
(case when a.fqty<0 or a.fqty+b.f<0 then 0
else a.fqty+b.f
end),
(case when a.fqty+b.f<0 then a.fqty+b.f else 0 end)
from tbltemp a inner join cte b on a.noid=b.noid+1
)select noid,fQty from cte
/*
noid fQty
----------- -----------
1 100
2 0
3 0
4 20
5 0
6 10(6 行受影响)*/
go
drop table tbltemp
select *,(case when fqty>=0 then 0 else fqty end)as f from tblTemp where noid=1
union all
select a.noid,
(case when a.fqty<0 or a.fqty+b.f<0 then 0
else a.fqty+b.f
end),
(case when a.fqty+b.f<0 then a.fqty+b.f else 0 end)
from tbltemp a inner join cte b on a.noid=b.noid+1
)select noid,fQty from cte 这句搞不懂
select *,(case when fqty>=0 then 0 else fqty end)as f from tblTemp where noid=1
union all
select a.noid,
(case when a.fqty<0 or a.fqty+b.f<0 then 0
else a.fqty+b.f
end),
(case when a.fqty+b.f<0 then a.fqty+b.f else 0 end)
from tbltemp a inner join cte b on a.noid=b.noid+1
)select noid,fQty from cte 这一句执行有错误呀
如果你还是用的 SQL SERVER 2000,那就得用一段循环程序.
insert into tblTemp select 1, 100
insert into tblTemp select 2, -40
insert into tblTemp select 3, 10
insert into tblTemp select 4, 50
insert into tblTemp select 5, -20
insert into tblTemp select 6, 30
go
select *,(case when fqty>=0 then 0 else fqty end)as f into #t from tblTemp where noid=1
while exists(select 1 from tbltemp where not exists(select 1 from #t where noid=tbltemp.noid))
insert into #t
select a.noid,
(case when a.fqty<0 or a.fqty+b.f<0 then 0
else a.fqty+b.f
end),
(case when a.fqty+b.f<0 then a.fqty+b.f else 0 end)
from tbltemp a inner join #t b on a.noid=b.noid+1
where b.noid=(select max(noid) from #t)
select noid,fQty from #t order by noid
/*
noid fQty
----------- -----------
1 100
2 0
3 0
4 20
5 0
6 10(6 行受影响)*/
go
drop table tbltemp,#t