怎样将b表中所有字段cWhCode数据相同的iQuantity相加,并将相加的结果加入到重新建立的a表中?

dxlth 2003-04-03 11:38:47
例如:
表a
iIsId int identity (1,1) not null,
cCode varchar(20) not null ,
cName varchar(40) null ,
benyueqichu float default 0 ,
benyueruku float default 0 ,
benyuechuku float default 0 ,
benyueleft float default 0 )
表b
iIsId int identity (1,1) not null,
dDate DateTime not null ,
cMth varchar(6) not null ,
cOrderNo varchar(12) not null ,
dInDate DateTime null ,
iLineNo int not null ,
cCaseNo varchar(20) null ,
cCode varchar(20) not null ,
cName varchar(40) null ,
cStd varchar(30) null ,
cUnit varchar(10) null ,
iQuantity float default 0 ,
iOutQty float default 0 ,
iBalQty float default 0 ,
iPrice float default 0 ,
cWhCode varchar(20) null ,
cWhName varchar(40) null ,
cPrdCCode varchar(10) null ,
cPrdCName varchar(40) null ,
cInType varchar(10) null ,
cRemark varchar(60) null )

...全文
33 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
pengdali 2003-04-03
重新建立的a:
select iIsId int identity(1,1),cCode,cName,sum(iQuantity) benyueqichu,0 benyueruku,0 benyuechuku,0 benyueleft into 表a from 表b where group by cCode

??
回复
happydreamer 2003-04-03
insert a(cCode,用哪个字段啊)
select cCode,sum(iQuantity) from b group by cCode
回复
misfans 2003-04-03
declare curCount cursor for
select cWhCode,sum(iQuantity) as count from b group by cWhCode
declare @vcWHCode varchar(20),@viQuantityf float
open curCount
fetch next from curCount into vcWhCode,@viQuantity
insert into a ...
fetch next from curCount into vcWhCode,@viQuantity
close curCount

ok?
回复
insert into A (cCode, benyueruku)
select cCode, sum(iQuantity) from B group by cCode
回复
ben988211 2003-04-03
insert into a select cWhCode,'',iQuantity,0,0,0 from (select cWhCode,sum(iQuantity) as iQuantity from b group by cWhCode) c
回复
dxlth 2003-04-03
是b表中所有字段cCode数据相同的iQuantity相加,并将相加的结果加入到重新建立的a

是cCode不是cWhCode
回复
caiyunxia 2003-04-03
update a set benyueqichu = iQuantity from
(select cCode,sum(iQuantity) as iQuantity from a group by cCode) c
where a.ccode=c.code

insert into a(...)
select ...
from (select cCode,sum(iQuantity) as iQuantity from a group by cCode) c
left a
on a.ccode=c.code
where a.ccode is null
回复
leimin 2003-04-03
insert into a(culumnname...)
select cCode,sum(iQuantity) from b group by cCode
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2003-04-03 11:38
社区公告
暂无公告