22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([Name] varchar(4),[Exp_Money] int)
insert [a1]
select '张三',90 union all
select '李四',30 union all
select '李四',20 union all
select '赵五',110 union all
select '孙六',300 union all
select '赵五',20 union all
select '张三',30
--> 测试数据:[b1]
if object_id('[b1]') is not null drop table [b1]
create table [b1]([Name] varchar(4),[Storage] int,[Balance] sql_variant)
insert [b1]
select '张三',1000,null union all
select '赵五',1000,null union all
select '孙六',500,null union all
select '李四',1000,null
---------------------查询开始---------------------------------
update [b1] set Balance=Storage-消费
from (
select sum([Exp_Money]) 消费,[Name] from [a1] group by [Name]
) t
where [b1].[Name]=t.[Name]
select * from [b1]
-------------------结果----------------------------------------
Name Storage Balance
---- ----------- ----------------------------------------
/*
张三 1000 880
赵五 1000 870
孙六 500 200
李四 1000 950
(4 行受影响)
*/