说的还不够清楚? A 表 ID 总金额 1 2 3 B 表 ID 商品金额 1 5 1 6 1 7 2 3 3 4 3 8 总金额=商品金额的累计(根据ID分类汇总)
这样? create table t1(id int,totalMoney int) insert into t1 select 1,null union all select 2,null ----------------- create table t2(id int,mount int) insert into t2 select 1,10 union all select 1,10 union all select 1,10 union all select 1,10 union all select 2,20 union all select 2,20 ----------------- --Try It update t1 set totalmoney=(select sum(mount) from t2 group by id) from t1,t2 where t1.id=t2.id
--借用lingpei2008兄弟的数据 :) create table t1(id int,totalMoney int) insert into t1 select 1,null union all select 2,null union all select 3,null ----------------- create table t2(id int,mount int) insert into t2 select 1,5 union all select 1,6 union all select 1,7 union all select 2,3 union all select 3,4 union all select 3,8 ----------------- --Try It update t1 set totalmoney=b.mount from t1 a,(select id,sum(mount) as mount from t2 group by id)b where a.id=b.idselect * from t1 select * from t2drop table t1,t2
update @t set sum_sly = b.sum1 from @t a , (select id , sum(goods_price) as sum1 from @t1 group by id) b where a.id = b.idupdate #t set sum_sly = (select sum(goods_price) from #t1 where #t.id = #t1.id)
A 表
ID 总金额
1
2
3
B 表
ID 商品金额
1 5
1 6
1 7
2 3
3 4
3 8
总金额=商品金额的累计(根据ID分类汇总)
create table t1(id int,totalMoney int)
insert into t1
select 1,null
union all
select 2,null
-----------------
create table t2(id int,mount int)
insert into t2
select 1,10
union all
select 1,10
union all
select 1,10
union all
select 1,10
union all
select 2,20
union all
select 2,20
-----------------
--Try It
update t1
set totalmoney=(select sum(mount) from t2 group by id)
from t1,t2
where t1.id=t2.id
服务器: 消息 512,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。
create table t1(id int,totalMoney int)
insert into t1
select 1,null
union all
select 2,null
union all
select 3,null
-----------------
create table t2(id int,mount int)
insert into t2
select 1,5
union all
select 1,6
union all
select 1,7
union all
select 2,3
union all
select 3,4
union all
select 3,8
-----------------
--Try It
update t1
set totalmoney=b.mount
from t1 a,(select id,sum(mount) as mount from t2 group by id)b
where a.id=b.idselect * from t1
select * from t2drop table t1,t2
set sum_sly = b.sum1
from @t a , (select id , sum(goods_price) as sum1 from @t1 group by id) b
where a.id = b.idupdate #t
set sum_sly = (select sum(goods_price) from #t1 where #t.id = #t1.id)