---插入 insert into fkcb select isnull(aa.ckbh,1),bb.cbh,isnull(aa.number,0)-bb.cbh as cbh from xsb left outer join fkcb aa on bb.cbh=aa.cbh left outer join (select * from ckzlb where mr=1) cc on aa.ckbh=cc.ckbh--备份 select * into xsbckup form xsb
如果已在fkcb已经存在这个ckbh和cbh, 怎样写。要用update吗?select * into xsbckup from xsb,这样会创建一个新表的。 这个xsbckup我在物理上已经建了。只需要将xsb销售表的记录添加到xsbckup备份历史表里。
---插入 select * into #temp from fkcb delect fkcb insert into fkcb select isnull(aa.ckbh,1),bb.cbh,isnull(aa.number,0)-bb.cbh as cbh from xsb left outer join #temp aa on bb.cbh=aa.cbh left outer join (select * from ckzlb where mr=1) cc on aa.ckbh=cc.ckbh--备份 --没有 select * into xsbckup form xsb --已有 insert into xsbckup select * from xsb
create procdure 过程名 as begin insert fkcb select 1,cbh,sum(-sl) from xsb where cbh not in (select cbh from fkcb) group by cbh update fkcb set number=number-a.sum(-sl) from xsb where fkcb.cbh=a.cbh group by cbh insert xsbckup select * from xsb delete xsb end
--分组生成临时表 select ckbh,cbh,sum(sl) into #t from (select (select top 1 ckbh from ckzlb where mr=1) as ckbh,cbh,sl from xsb) b group by ckbh,cbh--修改 update A set number=number-sl from fkcb A,#t A where A.ckbh=B.ckbh and A.cbh=B.cbh --如要这条记录在fkcb的这个仓库以前没有则添加进去 insert fkcb(ckbh,cbh,number) select ckbh,cbh,-sl from #t A where not exists (select 1 from fkcb where A.ckbh=ckbh and A.cbh=cbh)--处理完后把xsb销售表的记录全都添加到xsbckup销售备份表里。 insert xsbckup select * from xsb--最后清空xsb. delete from xsb
create procedure 过程名 as begin select 1,cbh,sum(-sl) num into #temp from cbh group by cbh insert fkcb select * from #temp where cbh not in (select cbh from fkcb) update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh insert xsbckup select * from xsb delete xsb end
哦!我怎么这么苯呀! create procedure 过程名 as begin select 1,cbh,sum(-sl) num into #temp from cbh group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb end
create procedure 过程名 as begin select 1,cbh,sum(-sl) num into #temp from cbh group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp end
还有: 这一句要给字段名不然会报错: select 1 ***,cbh,sum(-sl) num into #temp from cbh group by cbh条件加一个(暂且不论默认是否会变化): where fkcb.cbh=a.cbh and fkcb.ckbh=1
嘻嘻!j老师,是sql版的好老师! create procedure 过程名 as begin select 1 对了,cbh,sum(-sl) num into #temp from cbh group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1 insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp end -------------- :)
刚刚才上来,to pengdali(大力) select 1 对了,cbh,sum(-sl) num into #temp from cbh group by cbh 为什么要用这个临时表。
fkcb.ckbh=1 这个1是从ckzlb仓库表里取得的默认库的值select ckbh,mr from ckzlb where mr=1 得到ckbh,(这个表在设计的时候一定会有一个默认值的,我在做的时候限定了)
into #temp from cbh不会是这么写的吧.我没有cbh这个表呀
create procedure 过程名 as begin select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1 insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp end -------------- :)
ckbh外键, cbh货品编码, number,库存量
1 10101 7
1 10102 3
1 10103 -5
2 10101 5
insert into fkcb
select isnull(aa.ckbh,1),bb.cbh,isnull(aa.number,0)-bb.cbh as cbh from xsb left outer join fkcb aa
on bb.cbh=aa.cbh
left outer join
(select * from ckzlb where mr=1) cc
on aa.ckbh=cc.ckbh--备份
select * into xsbckup form xsb
这个xsbckup我在物理上已经建了。只需要将xsb销售表的记录添加到xsbckup备份历史表里。
select * into #temp from fkcb
delect fkcb
insert into fkcb
select isnull(aa.ckbh,1),bb.cbh,isnull(aa.number,0)-bb.cbh as cbh from xsb left outer join #temp aa
on bb.cbh=aa.cbh
left outer join
(select * from ckzlb where mr=1) cc
on aa.ckbh=cc.ckbh--备份
--没有
select * into xsbckup form xsb
--已有
insert into xsbckup
select * from xsb
仓库资料表ckzlb
ckbh仓库编号,ckname仓库名称,mr默认出库仓
1 主仓库 1 为1表示默认出库仓
2 2号仓 0
3 3号仓 0 分仓资料表fkcb
ckbh外键, cbh货品编码, number,库存量
1 10101 10
1 10102 3
2 10101 5销售表xsb
cbh, sl
10101 3
10103 5
销售备份表xsbckup和销售表结构是一模一样.把销售表的记录添加到 Fkcb的默认仓库里(也就是mr为1的仓库).
同时fkcb.number:=fkcb.number-xsb.sl where ckbh=(ckzlb.mr的仓库编号)
如要这条记录在fkcb的这个仓库以前没有则添加进去。(cbh,-sl);处理完后把xsb销售表的记录全都添加到xsbckup销售备份表里。
最后清空xsb.
不知道是否理解。想用存储过程做。先谢谢各位.
as
begin
insert fkcb select 1,cbh,sum(-sl) from xsb where cbh not in (select cbh from fkcb) group by cbh
update fkcb set number=number-a.sum(-sl) from xsb where fkcb.cbh=a.cbh group by cbh
insert xsbckup select * from xsb
delete xsb
end
select ckbh,cbh,sum(sl) into #t from
(select (select top 1 ckbh from ckzlb where mr=1) as ckbh,cbh,sl from xsb) b group by ckbh,cbh--修改
update A set number=number-sl from fkcb A,#t A where A.ckbh=B.ckbh
and A.cbh=B.cbh --如要这条记录在fkcb的这个仓库以前没有则添加进去
insert fkcb(ckbh,cbh,number) select ckbh,cbh,-sl from #t A where not exists (select 1 from fkcb where A.ckbh=ckbh
and A.cbh=cbh)--处理完后把xsb销售表的记录全都添加到xsbckup销售备份表里。
insert xsbckup select * from xsb--最后清空xsb.
delete from xsb
as
begin
select 1,cbh,sum(-sl) num into #temp from cbh group by cbh
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh
insert xsbckup select * from xsb
delete xsb
end
它的生命周期是会话,我一般执行完procedure后它回自己消失吗?
应该会吧!学习中...
1 10101 7
1 10102 3
1 10103 -10 不是 -5
2 10101 5
批处理要先UPDATE 后 INSERT
create procedure 过程名
as
begin
select 1,cbh,sum(-sl) num into #temp from cbh group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
end
as
begin
select 1,cbh,sum(-sl) num into #temp from cbh group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
end
这一句要给字段名不然会报错:
select 1 ***,cbh,sum(-sl) num into #temp from cbh group by cbh条件加一个(暂且不论默认是否会变化):
where fkcb.cbh=a.cbh and fkcb.ckbh=1
create procedure 过程名
as
begin
select 1 对了,cbh,sum(-sl) num into #temp from cbh group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
end
--------------
:)
select 1 对了,cbh,sum(-sl) num into #temp from cbh group by cbh
为什么要用这个临时表。
where mr=1
得到ckbh,(这个表在设计的时候一定会有一个默认值的,我在做的时候限定了)
as
begin
select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
end
--------------
:)