LouisXIV(夜游神)等大侠请进，还是原来的 [怎么用SQL语句实现两表数据相减？]的问题！

[mjldnz3]

原来发贴请教过这个问题！也有了解决方案，但是发现原来的解决方案还是有问题！
原贴：http://community.csdn.net/Expert/topic/4904/4904037.xml?temp=.4650843


原则：表A减去表B（PNo相对应的Count）数据，在表A中从count小的先减
目的：（1）将表A减去表B（PNo相对应的Count）数据，在表A中从count小的先减
（2）在A表"OutOperate"中记录该行该减的数值。(OutOperate的默认值为0)

表A
ID PNo Count Sign OutOperate
1 E11 2000 s1 0
2 E11 4000 s3 0
3 E11 3000 s7 0
4 E25 2000 s11 0
5 E25 2100 s12 0
6 E09 6000 s22 0
.
.

B表
ID PNo OutCount
1 E11 6000
2 E25 3100
3 E09 5000
.
.
按上面说的规则（小的先减），执行后，表A的结果为：
表A
ID PNo Count Sign OutOperate
1 E11 2000 s1 2000
2 E11 4000 s3 1000
3 E11 3000 s7 3000
4 E25 2000 s11 2000
5 E25 2100 s12 1100
6 E09 6000 s22 5000
.
.
SQL语句怎么实现呢？！

[mjldnz3]

下面是第一个问题原来的解决方案

declare @tablea table
(
id int,
PNo char(3),
Count int,
Sign varchar(3),
OutOperate int
)
insert into @tablea
select 1,'E11',2000,'s1',0 union all
select 2,'E11',4000,'s3',0 union all
select 3,'E11',3000,'s7',0 union all
select 4,'E25',6605203,'s11',0 union all
select 5,'E25',6605203,'s12',0 union all
select 6,'E09',6000,'s22',0

declare @tableb table
(
id int,
PNo char(3),
OutCount int
)
insert into @tableb
select 1,'E11',2000 union all
select 2,'E25',1 union all
select 3,'E09',5000

--select * from @tablea
--select * from @tableb

update a
set Count=case when ((select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count)-OutCount)<0 then 0
when ((select sum(count) from @tablea where Pno=a.Pno and Count<a.Count)-OutCount)>0 then count
else (select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count)-OutCount end
from @tablea a inner join @tableb b
on a.pno=b.pno


select * from @tablea

一般情况下这个语句都可以正确执行，但是当@tablea中E25的值都相同时，就不正确了！

[mjldnz3]

下面是第二个问题原来的解决方案

declare @tablea table
(
id int,
PNo char(3),
Count int,
Sign varchar(3),
OutOperate int
)
insert into @tablea
select 1,'E11',2000,'s1',0 union all
select 2,'E11',4000,'s3',0 union all
select 3,'E11',3000,'s7',0 union all
select 4,'E25',6605203,'s11',0 union all
select 5,'E25',6605203,'s12',0 union all
select 6,'E09',6000,'s22',0

declare @tableb table
(
id int,
PNo char(3),
OutCount int
)
insert into @tableb
select 1,'E11',2000 union all
select 2,'E25',1 union all
select 3,'E09',5000

--select * from @tablea
--select * from @tableb

update a
set OutOperate=Count-case when ((select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count)-OutCount)<0 then 0
when ((select sum(count) from @tablea where Pno=a.Pno and Count<a.Count)-OutCount)>0 then count
else (select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count)-OutCount end
from @tablea a inner join @tableb b
on a.pno=b.pno


select * from @tablea

一般情况下这个语句都可以正确执行，但是当@tablea中E25的值都相同时，就不正确了！

各位大侠请帮忙呀，这个问题我搞了很久了，谢谢了！

[mjldnz3]

自己再支持下！：（

[mjldnz3]

很是麻烦啊，
“无法使用 SELECT INTO 语句向表 '#t1' 中添加标识列，该表中已有继承了标识属性的列 'id'。”
的问题，本来是可以采用把@tablea 中的标识列id排除掉，再插入#t1，但是我这里实际上是要保留@tablea 中的id，因为我在
/*第二個問題*/
select tmp=identity(int,1,1), * into #t1 from @tablea order by PNo,[count],[id]
update a
set OutOperate=Count-case when ((select sum(count) from #t1 where Pno=a.Pno and tmp<=a.tmp)-OutCount)<0 then 0
when ((select sum(count) from #t1 where Pno=a.Pno and tmp<a.tmp)-OutCount)>0 then count
else (select sum(count) from #t1 where Pno=a.Pno and tmp<=a.tmp)-OutCount end
from #t1 a inner join @tableb b
on a.pno=b.pno
------
执行后想用
update a set OutOperate=b.OutOperate from @tablea a, #t1 b where a.id=b.id
把OutOperate值改变。因为我真正要的结果就是要改变@tablea 中的OutOperate的值啊。

这些问题真是太麻烦了，怎么才能实现呢？

[mjldnz3]

select tmp=identity(int,1,1), * into #t from (Select TOP 100 Percent * from @tablea) AA order by PNo,[count],[id]
改为这句，好象可以插入数据了。

[playwarcraft]

1.
declare @tablea table
(
id int,
PNo char(3),
Count int,
Sign varchar(3),
OutOperate int
)
insert into @tablea
select 1,'E11',2000,'s1',0 union all
select 2,'E11',4000,'s3',0 union all
select 3,'E11',3000,'s7',0 union all
select 4,'E25',6605203,'s11',0 union all
select 5,'E25',6605203,'s12',0 union all
select 6,'E09',6000,'s22',0

declare @tableb table
(
id int,
PNo char(3),
OutCount int
)
insert into @tableb
select 1,'E11',2000 union all
select 2,'E25',1 union all
select 3,'E09',5000

update a
set Count=case when ((select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count)-OutCount)<=0 then 0
when ((select sum(count) from @tablea where Pno=a.Pno and Count<a.Count )-OutCount)>=0 then count
when ((select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count and id<=a.id )-OutCount)<=0 then 0
when ((select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count and id<a.id)-OutCount)>=0 then count
else (select sum(count) from @tablea where Pno=a.Pno and Count<=a.Count and id<=a.id)-OutCount
end
from @tablea a inner join @tableb b
on a.pno=b.pno

select * from @tablea
--delete @tablea where count=0

[playwarcraft]

錯了,還是不對:(

[playwarcraft]

--用臨時表吧,可以思路簡單點^^
declare @tablea table
(
id int,
PNo char(3),
Count int,
Sign varchar(3),
OutOperate int
)
insert into @tablea
select 1,'E11',2000,'s1',0 union all
select 2,'E11',4000,'s3',0 union all
select 3,'E11',3000,'s7',0 union all
select 4,'E25',6605203,'s11',0 union all
select 5,'E25',6605203,'s12',0 union all
select 6,'E09',6000,'s22',0
declare @tableb table
(
id int,
PNo char(3),
OutCount int
)
insert into @tableb
select 1,'E11',2000 union all
select 2,'E25',1 union all
select 3,'E09',5000

/*第一個問題*/
select tmp=identity(int,1,1), * into #t from @tablea order by PNo,[count],[id]
update a
set Count=case when ((select sum(count) from #t where Pno=a.Pno and tmp<=a.tmp)-OutCount)<0 then 0
when ((select sum(count) from #t where Pno=a.Pno and tmp<a.tmp)-OutCount)>0 then count
else (select sum(count) from #t where Pno=a.Pno and tmp<=a.tmp)-OutCount end
from #t a inner join @tableb b
on a.pno=b.pno

select [id],Pno,count,sign from #t order by id
drop table #t

/*第二個問題*/
select tmp=identity(int,1,1), * into #t1 from @tablea order by PNo,[count],[id]
update a
set OutOperate=Count-case when ((select sum(count) from #t1 where Pno=a.Pno and tmp<=a.tmp)-OutCount)<0 then 0
when ((select sum(count) from #t1 where Pno=a.Pno and tmp<a.tmp)-OutCount)>0 then count
else (select sum(count) from #t1 where Pno=a.Pno and tmp<=a.tmp)-OutCount end
from #t1 a inner join @tableb b
on a.pno=b.pno

select [id],Pno,count,sign,outOperate from #t1 order by id
drop table #t1

/*1*/
id Pno count sign
----------- ---- ----------- ----
1 E11 0 s1
2 E11 4000 s3
3 E11 3000 s7
4 E25 6605202 s11
5 E25 6605203 s12
6 E09 1000 s22

/*2*/
id Pno count sign outOperate
----------- ---- ----------- ---- -----------
1 E11 2000 s1 2000
2 E11 4000 s3 0
3 E11 3000 s7 0
4 E25 6605203 s11 1
5 E25 6605203 s12 0
6 E09 6000 s22 5000

[playwarcraft]

主要是當count有相同值時,where count<=a.count會使結果不對,
引入個臨時表tmp,應該問題可以解決了

[fcuandy]

学习

[mjldnz2]

之前从未写个事务，请问我把上面的语句写成存储过程（带事务）如下：
这样写正确吗？

CREATE PROCEDURE SP_Mytest
AS
begin
begin tran
select tmp=identity(int,1,1), * into #t from @tablea order by PNo,count,id
if @@error<>0
begin
--操作失败，则事务回滚
rollback tran
end
update a
set Count=case
when ((select sum(count) from #t where Pno=a.Pno and tmp<=a.tmp)-OutCount)<0 then 0
when ((select sum(count) from #t where Pno=a.Pno and tmp<a.tmp)-OutCount)>0 then count
else (select sum(count) from #t where Pno=a.Pno and tmp<=a.tmp)-OutCount end
from #t a inner join @tableb b
on a.pno=b.pno

if @@error<>0
begin
--操作失败，则事务回滚
rollback tran
end
select * from #t order by id
if @@error<>0
begin
--操作失败，则事务回滚
rollback tran
end
drop table #t
if @@error<>0
begin
--操作失败，则事务回滚
rollback tran
end
--如果操作执行正确，则提交事务
commit tran
end
GO

[mjldnz3]

:(