求助。怎么样才能外理完下面所示的表的数据后得到下面的结果:
把表里所有的负数都变成正数或0。 要求把负的数量和金额的值都平摊到表里数值为正数的品种的数量和金额上。
但表里的总金额和总数量不能变,表里不能再有负值。
用什么方法,具体怎么样才能实现。 谢谢大家了。create table #tmp_kc
(bh char(5) null,
kf char(5) null,
ph char(20) null,
kc decimal(14,2) null ,
dj decimal(14,2) null,
je decimal(14,2) null
)
insert into #tmp_kc
(bh,kf,ph,kc,dj,je)
select '10001','1','A00001',200,2.1,420
union
select '10001','1','A00002',-100,2.1,-210
union
select '10001','1','A00003',-105,3.0,-315
UNION
SELECT '10002','1','A00004',200,2.5,500
UNION
SELECT '10002','2','A00004',-100,2.5,-250
UNION
SELECT '10002','3','A00005',-150,2,-300
UNION
SELECT '10002','3','A00006',20,2.2,44
UNION
SELECT '10002','4','A00005',20,2,40
UNION
SELECT '10003','2','A00007',150,2,300
UNION
SELECT '10003','1','A00008',-5,2.5,-12.5
UNION
SELECT '10003','3','A00007',30,2,60
UNION
SELECT '10003','1','A00019',-10,2.1,-21
UNION
SELECT '10004','1','A00027',150,2,300
UNION
SELECT '10004','1','A00107',5,2.6,13
UNION
SELECT '10004','1','A00021',-6,2.2,-13.2
UNION
SELECT '10004','2','A00021',-5,2.2,-11
UNION
SELECT '10004','2','A00024',100,2.3,230
UNION
SELECT '10004','3','A00021',100,2.2,220
UNION
SELECT '10005','1','A00109',300,5.12,1536
UNION
SELECT '10005','1','A00201',50,5.2,260
UNION
SELECT '10005','2','A00201',-3,5.2,-15.6
UNION
SELECT '10005','2','A00203',10,5.5,55
UNION
SELECT '10005','3','A00037',-51,5,-255
UNION
SELECT '10005','4','A00203',50,5.5,275
UNION
SELECT '10006','1','A00212',500,1.8,900
UNION
SELECT '10006','2','A00212',-200,1.8,-360
UNION
SELECT '10006','2','A00215',10,1.6,16
UNION
SELECT '10006','3','A00212',100,1.8,180
UNION
SELECT '10007','1','A00221',10,7.1,71
UNION
SELECT '10007','2','A00221',-50,7.1,-355
UNION
SELECT '10008','1','A00229',100,6.5,650
UNION
SELECT '10008','3','A00229',-20,6.5,-13
UNION
SELECT '10009','2','A00231',10,1.5,15
UNION
SELECT '10009','2','A00232',-100,1.5,-150
UNION
SELECT '10009','4','A00401',50,1.7,85
UNION
SELECT '10010','2','A00402',100,3,300
UNION
SELECT '10010','2','A00403',-20,3.1,-62
UNION
SELECT '10010','3','A00405',-30,3.2,-96
UNION
SELECT '10011','2','A00050',100,3,300
UNION
SELECT '10011','3','A00051',300,3.1,930
UNION
SELECT '10011','4','A00051',-100,3.1,-310
UNION
SELECT '10012','3','A00055',300,1.1,330
UNION
SELECT '10012','4','A00055',30,1.1,33
UNION
SELECT '10012','4','A00056',10,1.3,13
UNION
SELECT '10013','1','A00057',300,5,150
UNION
SELECT '10013','4','A00058',30,4.8,144
UNION
SELECT '10014','2','A00059',30,3,90
UNION
SELECT '10015','1','A00501',100,3.5,350
UNION
SELECT '10015','2','A00502',-30,3.2,-96
UNION
SELECT '10015','2','A00502',30,3.2,96
UNION
SELECT '10016','4','A00507',-1,3,-3
UNION
SELECT '10017','3','A00509',100,0.12,12
把表里所有的负数都变成正数或0。 要求把负的数量和金额的值都平摊到表里数值为正数的品种的数量和金额上。
但表里的总金额和总数量不能变,表里不能再有负值。
用什么方法,具体怎么样才能实现。 谢谢大家了。create table #tmp_kc
(bh char(5) null,
kf char(5) null,
ph char(20) null,
kc decimal(14,2) null ,
dj decimal(14,2) null,
je decimal(14,2) null
)
insert into #tmp_kc
(bh,kf,ph,kc,dj,je)
select '10001','1','A00001',200,2.1,420
union
select '10001','1','A00002',-100,2.1,-210
union
select '10001','1','A00003',-105,3.0,-315
UNION
SELECT '10002','1','A00004',200,2.5,500
UNION
SELECT '10002','2','A00004',-100,2.5,-250
UNION
SELECT '10002','3','A00005',-150,2,-300
UNION
SELECT '10002','3','A00006',20,2.2,44
UNION
SELECT '10002','4','A00005',20,2,40
UNION
SELECT '10003','2','A00007',150,2,300
UNION
SELECT '10003','1','A00008',-5,2.5,-12.5
UNION
SELECT '10003','3','A00007',30,2,60
UNION
SELECT '10003','1','A00019',-10,2.1,-21
UNION
SELECT '10004','1','A00027',150,2,300
UNION
SELECT '10004','1','A00107',5,2.6,13
UNION
SELECT '10004','1','A00021',-6,2.2,-13.2
UNION
SELECT '10004','2','A00021',-5,2.2,-11
UNION
SELECT '10004','2','A00024',100,2.3,230
UNION
SELECT '10004','3','A00021',100,2.2,220
UNION
SELECT '10005','1','A00109',300,5.12,1536
UNION
SELECT '10005','1','A00201',50,5.2,260
UNION
SELECT '10005','2','A00201',-3,5.2,-15.6
UNION
SELECT '10005','2','A00203',10,5.5,55
UNION
SELECT '10005','3','A00037',-51,5,-255
UNION
SELECT '10005','4','A00203',50,5.5,275
UNION
SELECT '10006','1','A00212',500,1.8,900
UNION
SELECT '10006','2','A00212',-200,1.8,-360
UNION
SELECT '10006','2','A00215',10,1.6,16
UNION
SELECT '10006','3','A00212',100,1.8,180
UNION
SELECT '10007','1','A00221',10,7.1,71
UNION
SELECT '10007','2','A00221',-50,7.1,-355
UNION
SELECT '10008','1','A00229',100,6.5,650
UNION
SELECT '10008','3','A00229',-20,6.5,-13
UNION
SELECT '10009','2','A00231',10,1.5,15
UNION
SELECT '10009','2','A00232',-100,1.5,-150
UNION
SELECT '10009','4','A00401',50,1.7,85
UNION
SELECT '10010','2','A00402',100,3,300
UNION
SELECT '10010','2','A00403',-20,3.1,-62
UNION
SELECT '10010','3','A00405',-30,3.2,-96
UNION
SELECT '10011','2','A00050',100,3,300
UNION
SELECT '10011','3','A00051',300,3.1,930
UNION
SELECT '10011','4','A00051',-100,3.1,-310
UNION
SELECT '10012','3','A00055',300,1.1,330
UNION
SELECT '10012','4','A00055',30,1.1,33
UNION
SELECT '10012','4','A00056',10,1.3,13
UNION
SELECT '10013','1','A00057',300,5,150
UNION
SELECT '10013','4','A00058',30,4.8,144
UNION
SELECT '10014','2','A00059',30,3,90
UNION
SELECT '10015','1','A00501',100,3.5,350
UNION
SELECT '10015','2','A00502',-30,3.2,-96
UNION
SELECT '10015','2','A00502',30,3.2,96
UNION
SELECT '10016','4','A00507',-1,3,-3
UNION
SELECT '10017','3','A00509',100,0.12,12
表中的kf,ph是什么意思?
表内的总数量和总金额不能变。表里的KF是商品所在库房号,
PH是商品的批号。
商品号(BH)和批号(PH)一样的商品,它们的单价(DJ)是一样的。
金额(JE)=单价(DJ)*数量(KC)
bh char(5),
kf char(5),
ph char(20),
kc decimal(14,2),
dj decimal(14,2),
je decimal(14,2)
)
insert into @tmp_kc
(bh,kf,ph,kc,dj,je)
select '10001','1','A00001',200,2.1,420
union
select '10001','1','A00002',-100,2.1,-210
union
select '10001','1','A00003',-105,3.0,-315
UNION
SELECT '10002','1','A00004',200,2.5,500
UNION
SELECT '10002','2','A00004',-100,2.5,-250
UNION
SELECT '10002','3','A00005',-150,2,-300
UNION
SELECT '10002','3','A00006',20,2.2,44
UNION
SELECT '10002','4','A00005',20,2,40
UNION
SELECT '10003','2','A00007',150,2,300
UNION
SELECT '10003','1','A00008',-5,2.5,-12.5
UNION
SELECT '10003','3','A00007',30,2,60
UNION
SELECT '10003','1','A00019',-10,2.1,-21
UNION
SELECT '10004','1','A00027',150,2,300
UNION
SELECT '10004','1','A00107',5,2.6,13
UNION
SELECT '10004','1','A00021',-6,2.2,-13.2
UNION
SELECT '10004','2','A00021',-5,2.2,-11
UNION
SELECT '10004','2','A00024',100,2.3,230
UNION
SELECT '10004','3','A00021',100,2.2,220
UNION
SELECT '10005','1','A00109',300,5.12,1536
UNION
SELECT '10005','1','A00201',50,5.2,260
UNION
SELECT '10005','2','A00201',-3,5.2,-15.6
UNION
SELECT '10005','2','A00203',10,5.5,55
UNION
SELECT '10005','3','A00037',-51,5,-255
UNION
SELECT '10005','4','A00203',50,5.5,275
UNION
SELECT '10006','1','A00212',500,1.8,900
UNION
SELECT '10006','2','A00212',-200,1.8,-360
UNION
SELECT '10006','2','A00215',10,1.6,16
UNION
SELECT '10006','3','A00212',100,1.8,180
UNION
SELECT '10007','1','A00221',10,7.1,71
UNION
SELECT '10007','2','A00221',-50,7.1,-355
UNION
SELECT '10008','1','A00229',100,6.5,650
UNION
SELECT '10008','3','A00229',-20,6.5,-13
UNION
SELECT '10009','2','A00231',10,1.5,15
UNION
SELECT '10009','2','A00232',-100,1.5,-150
UNION
SELECT '10009','4','A00401',50,1.7,85
UNION
SELECT '10010','2','A00402',100,3,300
UNION
SELECT '10010','2','A00403',-20,3.1,-62
UNION
SELECT '10010','3','A00405',-30,3.2,-96
UNION
SELECT '10011','2','A00050',100,3,300
UNION
SELECT '10011','3','A00051',300,3.1,930
UNION
SELECT '10011','4','A00051',-100,3.1,-310
UNION
SELECT '10012','3','A00055',300,1.1,330
UNION
SELECT '10012','4','A00055',30,1.1,33
UNION
SELECT '10012','4','A00056',10,1.3,13
UNION
SELECT '10013','1','A00057',300,5,150
UNION
SELECT '10013','4','A00058',30,4.8,144
UNION
SELECT '10014','2','A00059',30,3,90
UNION
SELECT '10015','1','A00501',100,3.5,350
UNION
SELECT '10015','2','A00502',-30,3.2,-96
UNION
SELECT '10015','2','A00502',30,3.2,96
UNION
SELECT '10016','4','A00507',-1,3,-3
UNION
SELECT '10017','3','A00509',100,0.12,12DECLARE @tmp_kctmp TABLE (
bh char(5),
kf char(5),
ph char(20),
kc decimal(14,2),
dj decimal(14,2),
je decimal(14,2)
)
insert into @tmp_kctmp (bh,kf,ph,kc,dj,je)
select bh,kf,ph,sum(kc) kc,dj,sum(je) je from @tmp_kc group by bh,kf,ph,dj
update @tmp_kc set kc=0, je=0.00update b set kc=a.kc,je=a.je from @tmp_kc b,@tmp_kctmp a where a.bh=b.bh and a.ph=b.ph and a.kf=b.kf
select * from @tmp_kc
/***************************************************10001 1 A00001 200.00 2.10 420.00
10001 1 A00002 -100.00 2.10 -210.00
10001 1 A00003 -105.00 3.00 -315.00
10002 1 A00004 200.00 2.50 500.00
10002 2 A00004 -100.00 2.50 -250.00
10002 3 A00005 -150.00 2.00 -300.00
.
.
.
.
10015 1 A00501 100.00 3.50 350.00
10015 2 A00502 0.00 3.20 0.00 -- 其中就这两项编号,库房,批号,单价是相同的
10015 2 A00502 0.00 3.20 0.00 --
10016 4 A00507 -1.00 3.00 -3.00
10017 3 A00509 100.00 0.12 12.00
****************************************************/
这个地方写错, 应该用删除,然后再插入就对了
update @tmp_kc set kc=0, je=0.00
update b set kc=a.kc,je=a.je from @tmp_kc b,@tmp_kctmp a where a.bh=b.bh and a.ph=b.ph and a.kf=b.kf修改成下面代码
delete @tmp_kc
insert into @tmp_kc select * from @tmp_kctmp
但用上面的办法只能处理好同库房,同商品,同批次的。但如果同库房里同商品,同批次的数量和金额总和为正数还好弄。如果他们的总和还是一个负数,这就又得处理一下。
上面表里的商品库存负值有以下几种情况:
1。商品的总库存就是负值,但这个商品的几个批号有正数的。有负数的。里面又分为:
A。相同商品相同批次的库存有正的,有负的。
B。同商品不同批号的库存有正的。有负的。2。商品的总库存是正值,但这个商品的几个批号有正数的有负数的。里面又分为。
A。相同商品相同批次的库存有正的,有负的。
B。同商品不同批号的库存有正的。有负的。 同商品同批次的品种单价一样。
同商品不同批次的单价不一样。
(商品分布在不同的库房里)
所以只能要求,数量和金额可以随意平摊。
但得保证处理后。表内的总数量和总金额不能变,不能再有负值。