• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

一个表中用sum计出总和,然后更新到另一个表中,SQL语句如何写?

CsdnBoom 2008-04-05 08:31:30
MSSQl2000
请教一条语句如何把'表1'中相同的'GoodsID'的'Qty'之和更新到'表2'中的'Qty'

--------------
表1
GoodsID Qty
001 10
001 15
001 20
002 10
002 20
003 10
003 30

表2
GoodsID Qty
001
002
003
------------
...全文
367 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
conan304 2008-04-07
update a
set Qty=(select sum(qty) from 表1 where goodsid=a.goodsid group by goodsid)
from 表2 a
回复
pengxuan 2008-04-07
update t2 set qty=(select sum(qty) from t1 where t1.GoodsID=t2.GoodsID) from t2
回复
ojuju10 2008-04-07



update a set qty=(select sum(qty) from tab1 where a.goodsid=goodsid) from tab2 a
回复
wangxuelid 2008-04-07

/*

GoodsID Qty
------- -----------
001 45
002 30
003 40

(所影响的行数为 3 行)
*/


回复
wangxuelid 2008-04-07

if object_id('tb') is not null
drop table tb

create table tb (GoodsID varchar(3),Qty int)
insert into tb
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30

if object_id('tbb') is not null
drop table tbb

create table tbb (GoodsID varchar(3),Qty varchar(50))
insert into tbb
select '001',null union all
select '002',null union all
select '003',null



select * from tbb
select * from tb


update b set Qty=(select sum(Qty) from tb where GoodsID=b.GoodsID ) from tbb b


回复
pt1314917 2008-04-06

update 表2 set Qty=b.Qtys from 表2 a,(select goodsid,sum(qty)qtys from 表1 group by goodsid) b where a.goodsid=b.goodsid

回复
中国风 2008-04-06
update t2
set Qty=isnull(Sum(t1.Qty),0)--t1不存在的GoodsID时为0
from t1 right join t2 on t1.GoodsID=t2.GoodsID
回复
-狙击手- 2008-04-06
create table tb1 (GoodsID varchar(3),Qty int)
insert into tb1
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30
create table tb2 (GoodsID varchar(3),Qty int)
insert into tb2
select '001',null union all
select '002',null union all
select '003',null
go

update a
set a.qty = b.s
from tb2 a,(select goodsid,sum(qty) as s from tb1 group by goodsid) b
where a.goodsid = b.goodsid


select * from tb2

drop table tb1,tb2

/*

GoodsID Qty
------- -----------
001 45
002 30
003 40

(所影响的行数为 3 行)
*/

回复
update 表2
set Qty=(
select
sum(Qty)
from
表1
where
表2.GoodsID = 表1.GoodsID)
回复
dawugui 2008-04-05
create table tb1 (GoodsID varchar(3),Qty int)
insert into tb1
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30
create table tb2 (GoodsID varchar(3),Qty varchar(50))
insert into tb2
select '001',null union all
select '002',null union all
select '003',null
go

update tb2 set qty = (select sum(qty) from tb1 where tb1.goodsid = tb2.goodsid)

--或者

update tb2 set qty = isnull((select sum(qty) from tb1 where tb1.goodsid = tb2.goodsid),0)

select * from tb2

drop table tb1,tb2

/*
GoodsID Qty
------- --------------------------------------------------
001 45
002 30
003 40

(所影响的行数为 3 行)
*/
回复
Limpire 2008-04-05
--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (GoodsID varchar(3),Qty int)
insert into #1
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (GoodsID varchar(3),Qty varchar(50))
insert into #2
select '001',null union all
select '002',null union all
select '003',null

update a set a.Qty=b.Qty from #2 a join (select GoodsID,sum(Qty)Qty from #1 group by GoodsID) b on a.GoodsID=b.GoodsID

select * from #2

/*
GoodsID Qty
------- -------
001 45
002 30
003 40
*/
回复
liangCK 2008-04-05
update tb2
set qty=(select sum(qty) from tb1 where tb2.goodsid=tb1.goodsid)
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-05 08:31
社区公告
暂无公告