create table tb(cinvcode varchar(20) , iwcqty int, iky int, id int)
insert into tb values('501-2129-00' , 500.0 , .000000 ,1 )
insert into tb values('501-2129-00' , 500.0 , .000000 ,2 )
insert into tb values('314-4591-00' , 18000.0 ,16400.000000 ,3 )
insert into tb values('314-4591-00' , 5100.0 ,16400.000000 ,4 )
insert into tb values('314-4591-00' , 11300.0 ,16400.000000 ,5 )
goselect cinvcode , iwcqty , iky = 0 , id from
(
select * , px = (select count(*) from tb where cinvcode = t.cinvcode and id < t.id) + 1 from tb t
) t where px = 1
union all
select cinvcode , iwcqty , iky = iwcqty , id from
(
select * , px = (select count(*) from tb where cinvcode = t.cinvcode and id < t.id) + 1 from tb t
) t where px <> 1
order by iddrop table tb/*
cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
501-2129-00 500 0 1
501-2129-00 500 500 2
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5(所影响的行数为 5 行)
*/
insert into tb values('501-2129-00' , 500.0 , .000000 ,1 )
insert into tb values('501-2129-00' , 500.0 , .000000 ,2 )
insert into tb values('314-4591-00' , 18000.0 ,16400.000000 ,3 )
insert into tb values('314-4591-00' , 5100.0 ,16400.000000 ,4 )
insert into tb values('314-4591-00' , 11300.0 ,16400.000000 ,5 )
goselect cinvcode , iwcqty , iky = 0 , id from
(
select * , px = (select count(*) from tb where cinvcode = t.cinvcode and id < t.id) + 1 from tb t
) t where px = 1
union all
select cinvcode , iwcqty , iky = iwcqty , id from
(
select * , px = (select count(*) from tb where cinvcode = t.cinvcode and id < t.id) + 1 from tb t
) t where px <> 1
order by iddrop table tb/*
cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
501-2129-00 500 0 1
501-2129-00 500 500 2
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5(所影响的行数为 5 行)
*/
create table tb(cinvcode varchar(20) , iwcqty int, iky int, id int)
insert into tb values('501-2129-00' , 500 , 0 ,1 )
insert into tb values('501-2129-00' , 500 , 0 ,2 )
insert into tb values('314-4591-00' , 18000 ,16400 ,3 )
insert into tb values('314-4591-00' , 5100 ,16400 ,4 )
insert into tb values('314-4591-00' , 11300 ,16400 ,5 )
go
select cinvcode,iwcqty,
(select case when count(b.iwcqty)=0 and a.iky>a.iwcqty then a.iwcqty
when count(b.iwcqty)>0 and a.iky>a.iwcqty then a.iky-sum(b.iwcqty)
else 0 end from tb as b where
b.cinvcode=a.cinvcode and b.id>a.id) as iky,
id
from tb as a
drop table tb
insert into tb values('501-2129-00' , 500.0 , .000000 ,1 )
insert into tb values('501-2129-00' , 500.0 , .000000 ,2 )
insert into tb values('314-4591-00' , 18000.0 ,16400.000000 ,3 )
insert into tb values('314-4591-00' , 5100.0 ,16400.000000 ,4 )
insert into tb values('314-4591-00' , 11300.0 ,16400.000000 ,5 )
go
update t set iky = case when sl >=0 then iwcqty else case when sl+iwcqty >=0 then iwcqty+sl else 0 end end
from tb t,(select id,iky-(select sum(iwcqty) sl from tb where id>=a.id and cinvcode = a.cinvcode) sl from tb a) h
where t.id = h.id
select * from tb
go
drop table tb
/*
cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
501-2129-00 500 0 1
501-2129-00 500 0 2
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5
*/
from tb t,(select id,iky-(select sum(iwcqty) sl from tb where id>=a.id and cinvcode = a.cinvcode) sl from tb a) h
where t.id = h.id
select * from tb
go
潇洒老乌龟的
501-2129-00 500 0 1
501-2129-00 500 500 2--这里应该是0
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5cson_cson 的
我运行以后有的记录出现问题0160274 1766.0 879.000000 81
0160274 298.0 879.000000 82
0160274 1504.0 879.000000 83
0160274 299.0 879.000000 84
0160274 46.0 879.000000 85
0160274 1282.0 879.000000 86
得到结果
0160274 1766.0 0.0 81
0160274 298.0 -2252.0 82
0160274 1504.0 0.0 83
0160274 299.0 -449.0 84
0160274 46.0 -403.0 85
0160274 1282.0 0.0 86
想要的结果
0160274 1766.0 0 81
0160274 298.0 0 82
0160274 1504.0 0 83
0160274 299.0 00 84
0160274 46.0 0 85
0160274 1282.0 879 86
insert into tb values('501-2129-00' , 500 , 0 ,1 )
insert into tb values('501-2129-00' , 500 , 0 ,2 )
insert into tb values('314-4591-00' , 18000 ,16400 ,3 )
insert into tb values('314-4591-00' , 5100 ,16400 ,4 )
insert into tb values('314-4591-00' , 11300 ,16400 ,5 )
go
select cinvcode,iwcqty,
(select case when count(b.iwcqty)=0 and a.iky>a.iwcqty then a.iwcqty
when count(b.iwcqty)>0 and a.iky>a.iwcqty then a.iky-sum(b.iwcqty)
else 0 end from tb as b where
b.cinvcode=a.cinvcode and b.id>a.id) as iky,
id
from tb as a
drop table tb
就是同一个cinvcode,假如有4跳记录
那么iky的值假如是4000,4条记录就是4个4000
我要做的是同一个CINVCODE只要一个4000,然后分摊到iwcqty
18000.0 16400.000000
5100.0 16400.000000
11300.0 16400.000000
得到
18000.0 0.000000
5100.0 5100.000000
11300.0 11300.000000而如果右边小于左边,则取其中一个值就好其他为0
18000.0 800.000000
5100.0 800.000000
11300.0 800.000000
得到
18000.0 800.000000
5100.0 0.000000
11300.0 0.000000
insert into tb values('501-2129-00' , 500.0 , .000000 ,1 )
insert into tb values('501-2129-00' , 500.0 , .000000 ,2 )
insert into tb values('314-4591-00' , 18000.0 ,16400.000000 ,3 )
insert into tb values('314-4591-00' , 5100.0 ,16400.000000 ,4 )
insert into tb values('314-4591-00' , 11300.0 ,16400.000000 ,5 )
goselect cinvcode,iwcqty,iky = case when iky > 0 then
(case when (select max(iky) from tb where a.cinvcode = cinvcode) >=
(select sum(iwcqty) from tb where a.cinvcode = cinvcode and id >= a.id)
then iwcqty else 0 end ) else iky end,id
from
tb a
drop table tb/*cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
501-2129-00 500 0 1
501-2129-00 500 0 2
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5(所影响的行数为 5 行)
*/
insert into tb values('501-2129-00' , 500.0 , .000000 ,1 )
insert into tb values('501-2129-00' , 500.0 , .000000 ,2 )
insert into tb values('314-4591-00' , 18000.0 ,16400.000000 ,3 )
insert into tb values('314-4591-00' , 5100.0 ,16400.000000 ,4 )
insert into tb values('314-4591-00' , 11300.0 ,16400.000000 ,5 )
goselect cinvcode,iwcqty,iky =
case when iky > 0 then
(case when (select max(iky) from tb where a.cinvcode = cinvcode) >=
(select sum(iwcqty) from tb where a.cinvcode = cinvcode and id >= a.id)
then iwcqty else (
(select max(iky) from tb where a.cinvcode = cinvcode) -
(select sum(iwcqty) from tb where a.cinvcode = cinvcode and id > a.id)
)
end )
else iky end,id
from
tb a
drop table tb/*cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
501-2129-00 500 0 1
501-2129-00 500 0 2
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5(所影响的行数为 5 行)
*/
create table tb(cinvcode varchar(20) , iwcqty int, iky int, id int)
insert into tb select '0160274',1766.0, 879.000000, 81
insert into tb select '0160274',298.0 ,879.000000 ,82
insert into tb select '0160274',1504.0, 879.000000, 83
insert into tb select '0160274',299.0 ,879.000000 ,84
insert into tb select '0160274',46.0 ,879.000000 ,85
insert into tb select '0160274',1282.0, 879.000000, 86 go
update t set iky = case when sl >=0 then iwcqty else case when sl+iwcqty >=0 then iwcqty+sl else 0 end end
from tb t,(select id,iky-(select sum(iwcqty) sl from tb where id>=a.id and cinvcode = a.cinvcode) sl from tb a) h
where t.id = h.id
select * from tb
go
drop table tb
/*
cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
0160274 1766 0 81
0160274 298 0 82
0160274 1504 0 83
0160274 299 0 84
0160274 46 0 85
0160274 1282 879 86*/
运行后有问题数据
原始数据
D040865Z1A 320.0 1.000000 13
D040865Z1A 1.0 1.000000 14
D040865Z1A 15.0 1.000000 15
D040865Z1A 1.0 1.000000 16
D040865Z1A 6.0 1.000000 17
得到D040865Z1A 320.0 0.0 13
D040865Z1A 1.0 0.0 14
D040865Z1A 15.0 0.0 15
D040865Z1A 1.0 0.0 16
D040865Z1A 6.0 0.0 17
想要结果
D040865Z1A 320.0 1 13
D040865Z1A 1.0 0.0 14
D040865Z1A 15.0 0.0 15
D040865Z1A 1.0 0.0 16
D040865Z1A 6.0 0.0 17
insert into tb values('501-2129-00' , 500 , 0 ,1 )
insert into tb values('501-2129-00' , 500 , 0 ,2 )
insert into tb values('314-4591-00' , 18000 ,16400 ,3 )
insert into tb values('314-4591-00' , 5100 ,16400 ,4 )
insert into tb values('314-4591-00' , 11300 ,16400 ,5 )
go
select cinvcode,iwcqty,
(select case when count(b.iwcqty)=0 and a.iky>a.iwcqty then a.iwcqty
when count(b.iwcqty)>0 and a.iky>sum(b.iwcqty) then a.iky-sum(b.iwcqty)
else 0 end from tb as b where
b.cinvcode=a.cinvcode and b.id>a.id) as iky,
id
from tb as a
drop table tb
create table tb(cinvcode varchar(20) , iwcqty int, iky int, id int)
insert into tb values('501-2129-00' , 500.0 , .000000 ,1 )
insert into tb values('501-2129-00' , 500.0 , .000000 ,2 )
insert into tb values('314-4591-00' , 18000.0 ,16400.000000 ,3 )
insert into tb values('314-4591-00' , 5100.0 ,16400.000000 ,4 )
insert into tb values('314-4591-00' , 11300.0 ,16400.000000 ,5 )
insert into tb values('0160274' , 1766.0, 879.000000, 81)
insert into tb values('0160274' ,298.0, 879.000000 ,82 )
insert into tb values('0160274' ,1504.0, 879.000000 ,83)
insert into tb values('0160274' ,299.0, 879.000000 ,84 )
insert into tb values('0160274' ,46.0 ,879.000000 ,85 )
insert into tb values('0160274' ,1282.0, 879.000000, 86)--建個臨時表只是為了看起來清楚一些
select *, tmp=convert(int,0) into #t from tbupdate #t
set tmp=case when isnull((select sum(iwcqty) from #t A where cinvcode=#t.cinvcode and id>#t.id),0) >= (select top 1 iky from #t B where cinvcode=#t.cinvcode)
then 0
else case when (select sum(iwcqty) from #t C where cinvcode=#t.cinvcode and id>=#t.id) >= (select top 1 iky from #t D where cinvcode=#t.cinvcode)
then (select top 1 iky from #t E where cinvcode=#t.cinvcode)-isnull((select sum(iwcqty) from #t F where cinvcode=#t.cinvcode and id>#t.id),0)
else iwcqty end
end
select cinvcode, iwcqty, tmp as iky, id
from #t/*
cinvcode iwcqty iky id
-------------------- ----------- ----------- -----------
501-2129-00 500 0 1
501-2129-00 500 0 2
314-4591-00 18000 0 3
314-4591-00 5100 5100 4
314-4591-00 11300 11300 5
0160274 1766 0 81
0160274 298 0 82
0160274 1504 0 83
0160274 299 0 84
0160274 46 0 85
0160274 1282 879 86
*/drop table #t
drop table tb
(case when (select max(iky) from tb where a.cinvcode = cinvcode) >=
(select sum(iwcqty) from tb where a.cinvcode = cinvcode and id >= a.id) then iwcqty else 0 end ) else iky end,id
from
tb a
drop table tb